Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 

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:

  • A Call statement (Oracle style). For example:

    Tip

    You might get this error message in the Steel-Belted Radius Carrier log files:

    If 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.

Example: