Quick Start Guide to SQL Server 7 -- Part 4
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