Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Beginner
Sep 21, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Use Stored Procedures with Output Parameters

If you already know that the execution of your SQL statement will return a single row of data, then you should always prefer stored procedures with output parameters in place of single-row select statements.

There are several advantages of using stored procedures over embedding simple SQL statements in your application code. On top of all those advantages, what you save is instead of opening a recordset for fetching the data, you simply call a stored procedure with output parameters. When you use a recordset, the query results returned by the data source object include data and metadata. Often the metadata is much larger than the data or is a significant part of the query results. Because of this, you may want to use a stored procedure with output parameters instead.

For example, if you want to get the Name and Description of an Item identified by a Item Code what you can do is write a stored procedure which takes in one input paramter for Item Code and two output parameters for Name and Description. Here is a simple method which can be used to accomplish this:

Private Sub GetNameDescFromSampleTable(ByVal SampleIdNo As Long, _
ByRef SampleName As String, ByRef SampleDesc As String)
On Error GoTo ErrorHandler

Dim oConnection As ADODB.Connection ' Connection Object
Dim oCommand As ADODB.Command ' Command Object

' Create a new connection object
Set oConnection = New ADODB.Connection
' Open the connection
oConnection.Open "DSN=Sample", "sa", ""
' Create a new command object
Set oCommand = New ADODB.Command
' Set active connection of command object to the already 
' open connection
Set oCommand.ActiveConnection = oConnection
' Set the name of stored procedure in CommandText property
oCommand.CommandText = "sp_GetSampleNameAndDescription"
oCommand.CommandType = adCmdStoredProc
' Add input and output parameters to the 
' parameters collection of the command object
oCommand.Parameters.Append _
oCommand.CreateParameter("@SampleIdNo", adInteger, adParamInput, , _
oCommand.Parameters.Append _
oCommand.CreateParameter("@SampleName", adVarChar, adParamOutput, 20)
oCommand.Parameters.Append _
oCommand.CreateParameter("@SampleDesc", adVarChar, adParamOutput, 200)
' Call Execute of command object which 
' internally executes the stored procedure
' Take out the values of output parameters
SampleName = oCommand.Parameters("@SampleName")
SampleDesc = oCommand.Parameters("@SampleDesc")
' Close the connection and release connection and command objects
Set oConnection = Nothing
Set oCommand = Nothing
Exit Sub
' Display error message
MsgBox "Error Number = " & Err.Number & ", Description = " & _
Err.Description, vbCritical, "GetNameDescFromSampleTable Error"
End Sub
Deepak Pant
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date