January 25, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

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

Let's now look at another sample to demonstrate output parameters.

Consider this example stored procedure:

CREATE PROCEDURE GetAuthorNames@AuthorID id, @FirstName varchar(20) output, _              @LastName varchar(40) outputASSELECT @FirstName = au_fname, @LastName = au_lnameFROM AuthorsWHERE au_id = @AuthorIDRETURN

This procedure is called GetAuthorNames and accepts three parameters; an Author ID of type id (varchar), plus two output parameters, FirstName and LastName.

The procedure dips into the Authors database and sets the FirstName and LastName output parameters to the relevant au_fname and au_lname fields in the table.

Let's look at the Visual Basic code that could handle this.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim objParam1 As ParameterDim objParam2 As ParameterDim objParam3 As ParameterDim strAuthorID As String' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' objParam1/2/3, the parameters we're passing' strAuthorID, variable to hold requested author IDWith 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 = "GetAuthorNames".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withstrAuthorID = InputBox("Enter the author ID:", "Required", "341-22-1782")' Ask the user for the author IDSet objParam1 = objCommand.CreateParameter("AuthorID", _adVarChar, adParamInput, 11, strAuthorID)Set objParam2 = objCommand.CreateParameter("FirstName", _adVarChar, adParamOutput, 20)Set objParam3 = objCommand.CreateParameter("LastName", _adVarChar, adParamOutput, 40)' Create the parameters - both input and outputs types' Note that objParam1 is an input parameter and also' specifies a passed value, our strAuthorIDobjCommand.Parameters.Append objParam1objCommand.Parameters.Append objParam2objCommand.Parameters.Append objParam3' Add our parameter to objCommand's Parameters collection' You would typically do this straight after you create' the parameters. Also, you need to append these in the ' order the parameters are listed in the stored procedureobjCommand.Execute' Execute the stored procedure' Our parameters will be automatically passedIf IsNull(objParam2.Value) And IsNull(objParam2.Value) ThenMsgBox "The return parameters are null. So you " & _"probably entered an invalid author ID number!", _vbExclamation, "No Such Author ID: " & strAuthorIDElseMsgBox "Author ID: " & strAuthorID & vbNewLine & _"First Name: " & objParam2.Value & vbNewLine & _"Last Name: " & objParam3.Value, vbInformationEnd If' Check the parameters and display the values appropriatelySet objParam = NothingSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

That's the end of our quick start guide to SQL Server 7. I hope you've enjoyed the series.

Page 10 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