Reading a recordset from a stored procedure is easy, essentially we just treat the procedure statement as the SQL statement, for example, something like:

sqlStr="USP_MyProc 1,2,3"
set rs=Server.CreateObject("adodb.Recordset")
rs.cursorlocation=aduseclient
rs.cachesize=1
rs.open sqlStr, conn, adOpenForwardOnly,adLockReadOnly

But how about reading more than one recordset from a procedure? Perhaps you have a circumstance where you need a couple of resultsets within the same code block. You could have more than one procedure and open each recordset in turn (or perhaps use the same procedure and control which resultset is returned using the procedure’s parameters).

Instead why not return all the required recordists from the one procedure using the one call to the database? It can be done, a procedure can return multiple recordists and each can be read in turn from your ASP code.

The trick is NextRecordset. You may already have been using this without quite realising, for example, when inserting a new record to the database that results in a unique identity value, we usually have a need to grab that value, so we’d do something like this:

sqlStr="INSERT INTO tblMyTable (myField) VALUES (myValue);"
Set rs = conn.execute (sqlStr & "select SCOPE_IDENTITY()").nextrecordset

If you look carefully you’ll see we’re actually running two queries, first the insert, and then a second to grab the identity. You can do the same with a procedure and multiple recordists. For example, let’s say you have a procedure that returns two recordists, you just cycle through them with NextRecordset:

sqlStr="USP_MyProc 1,2,3"
set rs=Server.CreateObject("adodb.Recordset")
rs.cursorlocation=aduseclient
rs.cachesize=1
rs.open sqlStr, conn, adOpenForwardOnly,adLockReadOnly
set rs2 = rs.NextRecordSet

Now you have the first results in rs and the second batch of results in rs2.

I was working on an ASP page today that included an AJAX call off to trigger various SQL updates, amongst which was a call to a stored procedure. The page started to error with:

Microsoft OLE DB Provider for SQL Server error ‘80040e31’
Timeout expired

The error line was the call to the stored procedure. After a bit of fiddling around I traced the issue to the ADO ConnectionTimeOut value. By default this is 30 seconds.

There are, I guess, 3 timeout values of interest on an ASP page:

1. Session timeout

How long the user’s browser session will last. On IIS this is defaulted to 20 minutes

2. Script timeout

How long a page can take to complete. On IIS this is defaulted to 90 seconds.

3. Connection timeout

How long a single call to the database can take to complete. This defaults to 30 seconds.

The stored procedure was taking > 30 seconds, hence throwing an error. I solved the problem by increasing the ConnectionTimeOut value for the particular call to the stored procedure, eg:

Dim cmdC
Set cmdC = Server.CreateObject("ADODB.Command")
with cmdC
.ActiveConnection = conn
.CommandText = "[procedure name]"
.CommandType =adCmdStoredProc
.CommandTimeout = 3000
.Execute
end with

The value is in seconds, so 3000 seconds = 5 minutes. You can read more about ConnectionTimeOut here.

Of course, there’s a whole other discussion why we have a stored procedure taking so long to run as part of an AJAX call. We’ll probably shift it off to a scheduled job or something eventually but we needed a quick fix for the interim.

Someone who’s pretty good programming on the front end, but not so crash hot on the backend, eg SQL Server, asked me the other day about looping through recordsets in a SQL Server Stored Procedure.

It is of course possible to use a cursor and FETCH but most SQL programmers frown on cursors in stored procedures. Cursors involve grabbing a bunch of records and locking them, there can be a substantial performance hit. They should really only be used as a last resort, for example, where you don’t have a good enough unique key value on your rows (although there are ways around that one usually as well).

The easiest way I know to look through records is to employ a temporary table, here’s a simple example:

-- Create a temporary table to hold the records to be updated
DECLARE @uniqueId int
DECLARE @TEMP TABLE (uniqueId int)
-- Insert into the temporary table a list of the records to be updated
INSERT INTO @TEMP (uniqueId)
SELECT uniqueId FROM myTable
-- Start looping through the records
WHILE EXISTS (SELECT * FROM @TEMP)
BEGIN
-- Grab the first record out
SELECT Top 1 @uniqueId = uniqueId FROM @TEMP
PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100))
-- Perform some update on the record
UPDATE myTable SET myField = 'something or other' WHERE uniqueId = @uniqueId
-- Drop the record so we can move onto the next one
DELETE FROM @TEMP WHERE uniqueId = @uniqueId
END