|
|
|
|
|
- Overview
- How to?
- Steps
- Running the stored procedure with HExecuteSQLQuery
- Remarks
SQL Server Native Connector: Execute the same stored procedure more than once
Disponible solo con este tipo de conexión
In some cases, it may be useful to execute the same stored procedure multiple times by changing one or more parameters. Several solutions can be implemented: - Execute the query directly (with HExecuteSQLQuery) as many times as necessary and change the desired parameter(s) each time.
- Prepare the query to be executed (with HPrepareSQLQuery) as well as the different parameters to be changed. Then, execute the query as many times as necessary with HExecuteSQLQuery. This second solution is much faster and optimizes the query result traversal time (in the case of a SELECT query).
This help page explains how the second solution can be implemented. Steps To prepare and execute a query multiple times: - Declare a data source. This data source will contain the result of the SQL query.
- 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:- or by initializing 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. Note: In some cases, the server does not accept 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.
- Prepare the query with HPrepareSQLQuery.
- Specify the value of the different parameters to take into account and run the query with HExecuteSQLQuery. Only the name of the data source that corresponds to the query must be specified.
This last step must be repeated as many times as necessary. Running the stored procedure with HExecuteSQLQuery - the connection name,
- the hQueryWithoutCorrection constant.
- the name of the procedure. 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 only the procedure name, the Native SQL Server Connector automatically adds the parameters of the stored procedure. Warning: If some parameters have a default value and are not referenced by the WLanguage code, a variable not referenced error will appear.. 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 to execute the procedure: p1 is a mandatory parameter and p2 is optional.. The following code triggers an error because the Native Connector access expected p2 in the query: MaProcédure is Data Source
MaProcédure.p1 = 10
IF HExecuteSQLQuery(MaProcédure, "MaConnexion", ...
hQueryWithoutCorrection, "MyProc") THEN
Trace("p1=" + MaProcédure.p1)
ELSE
Error(HErrorInfo())
END
The following code must be used: MaProcédure is Data Source
MaProcédure.p1 = 10
IF HExecuteSQLQuery(MaProcédure, "MaConnexion", ...
hQueryWithoutCorrection, "MyProc @p1, @p2 out") THEN
Trace("p1=" + MaProcédure.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.
Warning: the "@" character must be followed by at least one letter (the @1 syntax is forbidden).. The same parameter can be used several times. In this case, the corresponding variable will be reused. - 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.
- HPrepareSQLQuery cannot be used to prepare a stored procedure that returns records or columns. You must use HExecuteSQLQuery.
- The Native SQL Server Connector allows the re-execution of any queries that contain a user variable. You can re-run a query that uses a variable and change the value of this variable at each execution.
Example:
MaProcédure is Data Source
IF NOT HPrepareSQLQuery(MaProcédure, "MaConnexion", hQueryWithoutCorrection, ...
"SELECT * FROM AUTHORS WHERE au_lname like @p1") THEN
Error(HErrorInfo())
END
MaProcédure.p1 = "Hemingway"
HExecuteSQLQuery(MaProcédure)
HReadFirst(gSource)
MaProcédure.p1 = "Faulkner"
HExecuteSQLQuery(MaProcédure)
HReadFirst(gSource)
Esta página también está disponible para…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|