Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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, , _
SampleIdNo)
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
oCommand.Execute
' Take out the values of output parameters
SampleName = oCommand.Parameters("@SampleName")
SampleDesc = oCommand.Parameters("@SampleDesc")
' Close the connection and release connection and command objects
oConnection.Close
Set oConnection = Nothing
Set oCommand = Nothing
Exit Sub
ErrorHandler:
' 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.

 

 

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