January 25, 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 two parameters and updates records in a table.

CREATE PROCEDURE UpdateTitle @OldTitle varchar(80), _       @NewTitle varchar(80)ASUPDATE TitlesSET Titles.Title = @NewTitleWHERE Titles.Title = @OldTitle

This stored procedure is called UpdateTitle. It accepts two arguments, an OldTitle of type varchar(80) and a NewTitle also of type varchar(80).

In this SQL statement, both the OldTitle and NewTitle arguments refer to the Title field in the Titles table. This is of type varchar(80), hence why the parameters are also of the type varchar(80). You can view the field data types by viewing the properties of a table (as before).

This stored procedure doesn't return anything. It just updates the base table.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim Params(1 To 2) As Variant' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' Params(1 To 2), a variant array to hold the two'  arguments this stored procedures requires'   NOTE: When passing arrays across to stored procedures'   you must declare them as the variant type, unless'   you like the phrase 'major problems'...With 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 = "UpdateTitle".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withParams(1) = InputBox("Enter the book title to change:", "Book Title", "Net Etiquette")Params(2) = InputBox("Enter the new title for " & strParams(1), "New Title", "My New Title")' Put the old title and new title into the array,' ready to pass as arguments to the stored procedureobjCommand.Execute , Params' Execute the stored procedure, passing it the parameterSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

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