Working with Stored Procedures in MS-SQL
A simple example of a stored procedure returns a result set in the same way as a Select statement. For example, assume you have a table with the following fields: username, password, Alias, and active, where all fields have the datatype varchar. You want a stored procedure that will return a password and alias when the username and password received in the request match entries in the database, provided that active field has a value of yes.
Example 1
Example 1
To create a simple stored procedure, run the following command sequence from MS Query Analyzer to create a stored procedure called rsp_getpword.
This stored procedure can then be executed from a *.aut file as follows:
Example 2
Example 2
More complex stored procedures take input and output parameters in a manner similar to that used by Oracle. For example, assume you have a table with the following fields: username, password, profile, and active, where all fields have a datatype of varchar. You want a stored procedure that returns a password and profile when the username and password received in the request match a username and password in the database, provided that the active field has a value of yes.
First, to create the stored procedure, run the following command from MS query analyzer:
This stored procedure can then be executed from a *.aut file as follows:
Tips on Using SQL Stored Procedures
Tips on Using SQL Stored Procedures
Calling Stored Procedures
Calling Stored Procedures
There are two common methods for calling a stored procedure in Steel-Belted Radius Carrier *.aut configuration files:
The Execute statement. For example:
SQL= Execute rsp_getpword %username, %passwordA Call statement (Oracle style). For example:
SQL= {call rsp_authuser (%username!i, %password!o, %profile!o)}Tip You might get this error message in the Steel-Belted Radius Carrier log files:
diag 1: state = 24000, error = 0, [Microsoft][ODBC SQL Server Driver]Invalid cursor stateIf you do, use the Call statement method because it has better cursor handling.
Using the Insert Function
Using the Insert Function
When the INSERT function is used in a stored procedure, it must be encapsulated with the NOCOUNT function. This prevents the SQL database from returning a row count with the results.