|
|
|
|
|
- Overview
- How to?
- Steps
- Running the stored procedure with HExecuteSQLQuery
- Remarks
- Example 1: Stored procedure with return parameter
- 1. Declaring the data source
- 2. Declaring the variables used by the procedure
- 3. Running the stored procedure and retrieving the result
- Example 2: Stored function with return value
- 1. Declaring the data source
- 2. Declaring the variables used by the procedure
- 3. Running the stored procedure and retrieving the result
Native SQL Server Connector: Retrieving the output value of a stored procedure
Available only with this kind of connection
The Native SQL Server Connector is used to: - specify the input values during the call to a stored procedure.
- retrieve the output values after a call to a stored procedure.
Steps To specify and retrieve values when running a "Stored procedure" query, you must: - Declare a data source. This data source will contain the result of stored SQL procedure.
- Declare the variables of the stored procedure (input variables and output 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. These conversions may cause unexpected rounding or formatting errors (a Date changed into a string for example). Therefore, we advise you to specify the WLanguage types of variables with great care before running the stored procedure. To do so:- initialize the value of the variable (simple types: integer, string, float)
- specify the expected type with the Type property (not supported in this version)
The name of these variables must correspond to the name of the procedure parameters on SQL Server. Remark: 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. - Run the stored procedure with HExecuteSQLQuery.
Running the stored procedure with HExecuteSQLQuery - the connection name,
- the hQueryWithoutCorrection constant
- the name of the procedure, 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 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
Remarks - 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 reused. - You cannot retrieve a stored procedure that returns records (SELECT ... FROM ...), columns.
- Return value (functions): To retrieve the return value, you must declare a variable and assign the function result to this variable.
- To execute a query without enabling the bind option, use the hNoBind constant in HExecuteSQLQuery.
- Using RAISERROR: The errors whose severity is greater than 10 are fatal errors. Therefore, no error is generated by RAISERROR for an error whose severity is less than or equal to 10.
Example 1: Stored procedure with return parameter The stored procedure named MyProc returns the sum of @p1 and @p2 in @res. The return value is 2*@res. The code used to create this procedure on the server is as follows: CREATE PROCEDURE MyProc(@p1 int, @p2 int, @res int output) AS select @res=@p1+@p2 return 2*@res 1. Declaring the data source The data source is used to handle the variables of the procedure that must be run. MyProcedure is Data Source
2. Declaring the variables used by the procedure The variables handled by the procedure are declared from the data source. MyProcedure.p1 = 10 MyProcedure.p2 = 20
Remark: There is no need to declare all the variables used. In this example, the variable used to retrieve the result of the procedure is not declared. 3. Running the stored procedure and retrieving the result IF HExecuteSQLQuery(MyProcedure, "MyConnection", hQueryWithoutCorrection, ... "MyProc @p1,@p2,@res out") THEN Trace("res=" + MyProcedure.res) Trace("p1=" + MyProcedure.p1) Trace("p2=" + MyProcedure.p2) ELSE Error(HErrorInfo()) END
Example 2: Stored function with return value The stored function named MyProc returns the sum of @p1 and @p2in @res. The return value is 2*@res. The code used to create this procedure on the server is as follows: CREATE PROCEDURE MyProc(@p1 int, @p2 int, @res int output) AS select @res=@p1+@p2 return 2*@res 1. Declaring the data source The data source is used to handle the variables of the procedure that must be run. MyProcedure is Data Source
2. Declaring the variables used by the procedure The variables handled by the procedure are declared from the data source. MyProcedure.p1 = 10 MyProcedure.p2 = 20
Remark: There is no need to declare all the variables used. In this example, the variable used to retrieve the result of the procedure is not declared. 3. Running the stored procedure and retrieving the result IF HExecuteSQLQuery(MyProcedure, "MyConnection", ... hQueryWithoutCorrection, "set @res=user.MyProc(@p1,@p2)") THEN Trace("res=" + MyProcedure.res) Trace("p1=" + MyProcedure.p1) Trace("p2=" + MyProcedure.p2) ELSE Error(HErrorInfo()) END
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|