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)
AS
SELECT password, alias FROM authentication WHERE username = @Uname
AND 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=1
Alias=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)OUTPUT
AS
SELECT @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 ON
IF (@Fail = 0) BEGIN
INSERT INTO AccessList (MemberID, MemberPassword, MemberLabel, MemberBill, AccessDate)
VALUES (@InID, @InPass, 'test', 'test', Getdate())
END ELSE BEGIN
INSERT INTO AccessFailures (FailedUser, FailedPassword, AttemptTime, FailureReason)
VALUES (@InID, @InPass, Getdate(), 'test')
END
SET NOCOUNT OFF
GO

Modified: 2018-01-11