How to: Reference: |
Microsoft SQL Server stored procedures are supported using SQL Passthru. These procedures need to be developed within Microsoft SQL Server using the CREATE PROCEDURE command.
The adapter supports stored procedures with input, output, and in-out parameters.
The output parameter values that are returned by stored procedures are available as result sets. These values form a single-row result set that is transferred to the client after all other result sets are returned by the invoked stored procedure. The names of the output parameters (if available) become the column titles of that result set.
Note that only the output parameters (and the returned value) referenced in the invocation string are returned to the client. As a result, users have full control over which output parameters have their values displayed.
The server supports invocation of stored procedures written according to the rules of the underlying DBMS. Note that the examples shown in this section are SQL-based. See the DBMS documentation for rules, languages, and additional programming examples.
SQL MSODBC EX procname [parameter_specification1] [,parameter_specification2]... END
where:
Is the ENGINE suffix for Microsoft SQL Server.
Is the name of the stored procedure. It is the fully or partially qualified name of the stored procedure in the native RDBMS syntax.
You can employ either SQL or SYS naming conventions to control the separator character used for interpreting multipart names, as described in Setting Naming Conventions.
IN, OUT, and INOUT parameters are supported. Use the variation required by the stored procedure:
Is a literal (for example, 125, 3.14, 'abcde'). You can use reserved words as input. Unlike character literals, reserved words are not enclosed in quotation marks (for example, NULL). Input is required.
Is represented as a question mark (?). You can control whether output is passed to an application by including or omitting this parameter. If omitted, this entry will be an empty string (containing 0 characters).
Consists of a question mark (?) for output and a literal for input, separated by a slash: /. (For example: ?/125, ?/3.14, ?/'abcde'.) The out value can be an empty string (containing 0 characters).
In this example, a user invokes a stored procedure, edaqa.test_proc01, supplies input values for parameters 1, 3, 5 and 7, and requests the returned value of the stored procedure, as well as output values for parameters 2 and 3.
Note that parameters 4 and 6 are omitted; the stored procedure will use their default values, as specified at the time of its creation.
SQL MSODBC EX edaqa.test_proc01 125,?,?/3.14,,'abc',,'xyz' END
This stored procedure uses out and inout parameters:
CREATE PROCEDURE EDAQA.PROCP3 ( OUT chSQLSTATE_OUT CHAR(5), OUT intSQLCODE_OUT INT, INOUT l_name char(20), INOUT f_name char(20)) RESULT SETS 1 LANGUAGE SQL ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN -- Declare variable DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; -- Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT EDAQA.NF29005.SSN5 AS SSN5, EDAQA.NF29005.LAST_NAME5 AS LAST_NAME5, EDAQA.NF29005.FIRST_NAME5 AS FIRST_NAME5, EDAQA.NF29005.BIRTHDATE5 AS BIRTHDATE5, EDAQA.NF29005.SEX5 AS SEX5 FROM EDAQA.NF29005 WHERE ( ( EDAQA.NF29005.LAST_NAME5 = l_name ) AND ( EDAQA.NF29005.FIRST_NAME5 = f_name ) ); -- Cursor left open for client application OPEN cursor1; SET chSQLSTATE_OUT = SQLSTATE; SET intSQLCODE_OUT = SQLCODE; SET l_name = 'this is first name'; SET f_name = 'this is last name'; END P1 @
You can capture application errors using the RAISERROR method. Any application error that is issued by the stored procedure is available in the server variable &MSSMSGTXT.
WebFOCUS |