Why you should always use a recordset when executing stored procedures

I’ve talked about using a recordset with stored procedures before, but this time I wanted to emphasize the importance of always using a recordset when executing a stored procedure from Accerss VBA.

Command Object does not cut it

Usually a programmer will use a command object to execute a stored procedure that does not return records. The format is:

ExecuteMyCommand “Exec usp_TestProcedure 1”

Where the procedure name is usp_TestProcedure and the number 1 is an argument. ExecuteMyCommand is from my EasyADODB library. The problem with this approach is the lack of feedback you get back from SQL Server: Did the procedure execute ok? Where all the transactions completed? There is no easy way to return the results back to Access unless you use additional code.

Use a Recordset Instead

When you use a recordset to execute the stored procedure, you can easily return values back from your stored procedure and determine if all is well. First, here is the stored procedure we are going to use for testing:

CREATE PROCEDURE usp_TestProcedure

@TestError Int

AS

IF @TestError = 1
Select 1 AS MyResult
ELSE
Select 0 AS MyResult
END

usp_TestPrcedure will return a recordset with just one field: MyResult. Here is an example that returns 1 from it:

Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = “Exec usp_TestProcedure 1”
OpenMyRecordset rs, strSQL
With rs
MsgBox “Your procedure returned ” & !MyResult
End With

Start using Recordsets with all of your stored preocedures today!