February 28, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

  • By Karl Moore
  • Send Email »
  • More Articles »

Passing information back to your Visual Basic program via stored procedures is commonly done via recordsets. But that isn't always the best solution.

Sometimes you may want to pass your procedure an author ID, and have it return just the author's first and last names. Or perhaps you want a procedure to tell you exactly how many customers you currently have. Maybe you simply want to return a custom error message.

You can do all this via output parameters.

Let's look at an example stored procedure:

CREATE PROCEDURE Hello @HelloMsg varchar(50) outputASSET @HelloMsg="Good Morning from SQL Server!"RETURN

This procedure is called Hello and accepts a typical varchar parameter called HelloMsg. However note that it's followed by the 'output' keyword.

The procedure then sets the HelloMsg parameter to a simple message, and finishes off cleanly with the RETURN statement (not required but certainly neat, akin to an Exit Function command).

Let's now look at some Visual Basic code that could handle this. On the next page, we'll look at an example both more complicated and useful.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim objParam As Parameter' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' objParam, the parameter we're passingWith objConn.ConnectionString = "Driver=SQL Server;Server=COLOSSI;" & _"Database=PUBS;User ID=KarlMoore;Password=TEST".OpenEnd With' Get a connection to the databaseWith objCommand.CommandType = adCmdStoredProc.CommandText = "Hello".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withSet objParam = objCommand.CreateParameter("TheHelloMessage", _adVarChar, adParamOutput, 50)' Create the parameter, stating its data type' and that it is an output parameter (adParamOutput).' The name of the parameter doesn't matter, but if' you're passing more than one, do so in the order' they're listed in the stored procedure.objCommand.Parameters.Append objParam' Add our parameter to objCommand's Parameters collectionobjCommand.Execute' Execute the stored procedure' Our parameter will be automatically passedMsgBox objParam.Value' After the stored procedure is altered, any return' value will now be inside objParam.ValueSet objParam = NothingSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Page 9 of 10

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

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