PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • How to proceed?
  • The steps
  • Running the stored procedure with HExecuteSQLQuery
  • Notes
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Native SQL Server Connector: Running the same stored procedure several times
Native AccessesAvailable only with this kind of connection
Overview
In some cases, it may be interesting to run the same stored procedure several times while modifying one or more parameters.
Several solutions can be implemented:
  1. Run the query (with HExecuteSQLQuery) as many times as necessary while modifying the requested parameters.
  2. Prepare the query to run (HPrepareSQLQuery) as well as the different parameters to modify and run the query as many times as necessary with HExecuteSQLQuery. This second solution is much faster and it optimizes the time required to browse the query result (for a Select query).
This help page explains how this second solution can be implemented.
How to proceed?

The steps

To prepare and run a query several times, you must:
  1. Declare a data source. This data source will contain the result of the SQL query.
  2. Declare the types of the query variables.
    The variables are retrieved in the type specified in WLanguage. The variables are text variables by default.
    Therefore, conversions may occur between the native type of the database and the WLanguage type, causing unexpected rounding or formatting errors (Date transformed into string for example).
    Therefore, we advise you to specify the WLanguage types of the variables with great care before running the query. To do so:
    • initialize the value of the variable (simple types: integer, string, float).
    • specify the expected type with ..Type (not supported in this version).
      The name of these variables must correspond to the name of the procedure parameters on SQL Server.
      Note: In some cases, the server does not accept to perform an implicit conversion. A conversion error is displayed. In this case, you must initialize the type by defining any ordinary value in the selected type.
      For example, to define a numeric type, use MyQuery.Parameter=0.
  3. Prepare the query with HPrepareSQLQuery.
  4. Specify the value of the different parameters to take into account and run the query with HExecuteSQLQuery. Only the name of the data source corresponding to the query must be specified.
This last step must be repeated as many times as necessary.

Running the stored procedure with HExecuteSQLQuery

HExecuteSQLQuery must be used with:
  • the name of the connection,
  • the hQueryWithoutCorrection constant
If you are using Native SQL Server Connector via DB-Library, the procedure name must be specified.
If you are using Native SQL Server Connector via SQLnCli, the procedure name must be specified. The procedure name must be followed by the parameters (including the @ character). To specify the output parameters, you must specify 'out' or 'output'. The parameters must be separated by commas.
Compatible mode:
If the query contains the procedure name only, the Native Connector via SQLnCli automatically adds the parameters of the stored procedure.
Caution: If some parameters have a default value and if they are not referenced by the WLanguage code, a non-referencing error will occur on the variable. In this case, you must either modify the code for calling the query by defining the mandatory parameters, or explicitly declare the optional variable.
Example:
Code for creating the procedure:
CREATE PROCEDURE MyProc(@p1 int, @p2 int, @res int output)
AS set @res=@p1+@p2
Code for running the procedure: p1 is a mandatory parameter and p2 is an optional parameter. The following code triggers an error because the Native Connector access expected p2 in the query:
MyProcedure is Data Source
MyProcedure.p1 = 10
IF HExecuteSQLQuery(MyProcedure, "MyConnection", ...
hQueryWithoutCorrection, "MyProc") THEN
Trace("p1=" + MyProcedure.p1)
ELSE
Error(HErrorInfo())
END
The following code must be used:
MyProcedure is Data Source
MyProcedure.p1 = 10
IF HExecuteSQLQuery(MyProcedure, "MyConnection", ...
hQueryWithoutCorrection, "MyProc @p1, @p2 out") THEN
Trace("p1=" + MyProcedure.p1)
ELSE
Error(HErrorInfo())
END

Notes

  • The declared variables must be identical to the ones used. Otherwise, a WLanguage error occurs.
  • In the call to the stored procedure, you must use the syntax specific to the database used, including for the syntax of parameters. Therefore, for SQL Server, the parameters are specified with the following syntax: @ParamName.
    Caution: the "@" character must be followed by at least one letter (the syntax @1 is not allowed).
    The same parameter can be used several times. In this case, the corresponding variable will be re-used.
  • Return value (functions):
    • If you are using Native SQL Server Connector via DB-Library: The return value of the function is returned in a variable automatically defined by Native SQL Server Connector. This variable is named RETURN_VALUE.
    • If you are using Native SQL Server Connector via SQLnCli: To retrieve the return value, you must declare a variable and assign the function result to this variable.
  • To run a query without enabling the bind, use the hNoBind constant in HExecuteSQLQuery.
  • HPrepareSQLQuery cannot be used to prepare a stored procedure that returns records or columns. You must use HExecuteSQLQuery.
  • The Native SQL Server Connector via SQLnCli allows the re-execution of queries containing a user variable. You have the ability to re-run a query that is using a variable and to change the value of this variable at each execution.
    Example:
    MyProcedure is Data Source
    IF NOT HPrepareSQLQuery(MyProcedure, "MyConnection", hQueryWithoutCorrection, ...
    "SELECT * FROM AUTHORS WHERE au_lname like @p1") THEN
     Error(HErrorInfo())
    END

    MyProcedure.p1 = "Hemingway"
    HExecuteSQLQuery(MyProcedure)
    HReadFirst(gSource)

    MyProcedure.p1 = "Faulkner"
    HExecuteSQLQuery(MyProcedure)
    HReadFirst(gSource)
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment