Quick Start Guide to SQL Server 7 -- Part 4

Let's look at an example stored procedure that accepts a numeric ID and deletes all records from the Employee table with the passed Job ID.

CREATE PROCEDURE DeleteJobType @JobID smallintASDELETE FROM EmployeesWHERE Employees.Job_ID = @JobID

This stored procedure is called DeleteJobType. It accepts an ID number of type smallint. It doesn't return anything.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim intJobID As Integer' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' intJobID, an integer to hold the Job IDs to be deletedWith 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 = "DeleteJobType".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withintJobID = InputBox("Which job type do you wish to delete?", "Enter Job ID", 14)' Grab the Job ID from the userobjCommand.Execute , intJobID' Execute the stored procedure, passing it the parameterSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Page 7 of 10



This article was originally published on November 20, 2002