Steel-Belted Radius Carrier 7.2.x Administration and Configuration Guide > Configuring SQL Authentication > Working with Stored Procedures in MS-SQL

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