October 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Quick Start Guide to SQL Server 7 -- Part 4

  • November 19, 2002
  • 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



Comment and Contribute

 


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

 

 


Sitemap | Contact Us

Rocket Fuel