Visual Basic Instinct : Returning Values from Stored Procedures

Returning Values from Stored Procedures

In this article, I will discuss the benefits of the Command object over ordinary SQL statements to execute stored procedures, and explain how you can use it to return values from stored procedures. The article assumes familiarity with stored procedures in SQL.

Author: Jens G. Balchen

Introduction

Executing stored procedures is easy using plain SQL and ADO. The following will execute GetUserProfile with the user id as parameter:


' Assuming a connection has been established and a recordset has 
' been created
Recordset.Open "EXEC GetUserProfile " & UserId, Connection, _ 
   adOpenStatic, adLockOptimistic

The returned data will be in recordset form, which probably works fine in this case. Suppose in addition to the recordset you want to return some sort of status information or error code? In the previous example, you would have to include an extra column in your recordset, and fill in the return value in one (perhaps the first) or all records.

A more elegant solution is using the Command object and output parameters in SQL. A stored procedure can be declared like this:


CREATE PROCEDURE GetUserId @userid INT, @status INT OUTPUT
AS

...

This way, the value of @status will be returned to the caller when the procedure ends. How to fetch it? Use a Command object instead of plain SQL.

Using a Command object

The following code will create a new Command object, fill it with the data necessary to execute GetUserProfile, and then run it. This first time, we disregard the return value scenario and execute with user id only.


Dim Cmd As New ADODB.Command
Dim Param As ADODB.Parameter

   Cmd.CommandText = "GetUserId"
   Cmd.CommandType = CommandTypeEnum.adCmdStoredProc
   
   Set Param = Cmd.CreateParameter("userid", adInteger, adParamInput, 8, UserId)
   Cmd.Parameters.Append Param
   
   ' Assuming a connection has been established and a recordset has 
   ' been created
   Set Cmd.ActiveConnection = Connection
   Set Recordset = Cmd.Execute()

This way, the recordset will contain the exact same data as before. Now, if we add another parameter and alter adParamInput to adParamOutput, ADO will return the output value.


Dim Cmd As New ADODB.Command
Dim Param As ADODB.Parameter

   Cmd.CommandText = "GetUserId"
   Cmd.CommandType = CommandTypeEnum.adCmdStoredProc
   
   Set Param = Cmd.CreateParameter("userid", adInteger, adParamInput, 8, UserId)
   Cmd.Parameters.Append Param

   Set Param = Cmd.CreateParameter("status", adInteger, adParamOutput, 8, Status)
   Cmd.Parameters.Append Param
   
   ' Assuming a connection has been established and a recordset has 
   ' been created
   Set Cmd.ActiveConnection = Connection
   Set Recordset = Cmd.Execute()

The Status variable will contain whatever value was returned from the stored procedure.