December 21, 2014
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

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



Comment and Contribute

 


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

 

 


Enterprise Development Update

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

Sitemap | Contact Us

Rocket Fuel