March 4, 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 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

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