March 1, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

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

Let's look at an example stored procedure that accepts a parameter and returns a set of records to the client.

CREATE PROCEDURE SelectTitleByKeyword _       @keyword varchar(50)ASSELECT Title, Title_ID, TypeFROM TitlesWHERE Title LIKE '%' + @keyword + '%'

This stored procedure is called SelectTitleByKeyword. It accepts a keyword of type varchar(50), a string up to 50 characters long.

It then returns the results of our SQL statement, which searches for any items with the passed keyword in the title.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim objRS As RecordsetDim strBookTitle As String' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' objRS, to hold the returning recordset' strBookTitle, a string to hold the book keywordWith 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 = "SelectTitleByKeyword".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withstrBookTitle = InputBox("Enter a keyword from the book you are looking for:", _"Book Keyword", "computer")' Put the keyword into the array. We need to use arrays' for this, even if we're just working with one argument.Set objRS = objCommand.Execute(, strBookTitle)' Execute the stored procedure, passing it the parameterIf objRS.BOF = False And objRS.EOF = False ThenCall MsgBox("The first book I found containing your " & _"keyword was:" & vbNewLine & objRS.Fields("Title") & _vbNewLine & "The book ID number is " & _objRS.Fields("Title_ID") & vbNewLine & _"This book is classified under " & _objRS.Fields("Type"), vbInformation)ElseMsgBox ("No books contained the keyword: " & strBookTitle)End If' Hurrah! We now have a recordset to do what we want with!Set objRS = NothingSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

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