Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

Navigation  Back up to About Overview 
  
[+] Expand All
[-] Collapse All

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

To create a simple stored procedure, run the following command sequence from MS Query Analyzer to create a stored procedure called rsp_getpword.

CREATE PROCEDURE rsp_getpword@Uname varchar(21),@pword varchar(21)ASSELECT password, alias FROM authentication WHERE username = @UnameAND password = @pword AND active = 'yes'GO

This stored procedure can then be executed from a *.aut file as follows:

SQL= Execute rsp_getpword %username, %password[results]Password=1Alias=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:

CREATE PROCEDURE rsp_authuser@uname as varchar(20),@pword as varchar(21) OUTPUT,@profile as varchar(21)OUTPUTASSELECT @pword = password,@profile = profile FROM authentication WHERE username = @uname AND active = 'yes'GO

This stored procedure can then be executed from a *.aut file as follows:

SQL= {call rsp_authuser (%username!i, %password!o, %profile!o)}[results]; No entries should be specified in results, everything but the header should be commented out.

Tips on Using SQL 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, %password
  • A 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 state

    If you do, use the Call statement method because it has better cursor handling.

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:

SET NOCOUNT ONIF (@Fail = 0) BEGININSERT INTO AccessList (MemberID, MemberPassword, MemberLabel, MemberBill, AccessDate)VALUES (@InID, @InPass, 'test', 'test', Getdate())END ELSE BEGININSERT INTO AccessFailures (FailedUser, FailedPassword, AttemptTime, FailureReason)VALUES (@InID, @InPass, Getdate(), 'test')ENDSET NOCOUNT OFFGO

Modified: 2017-03-07