February 20, 2019
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 4

  • November 20, 2002
  • By Karl Moore
  • Send Email »
  • More Articles »

Let's look at an example stored procedure that accepts a number of parameters, then uses them to insert a row into a table.

CREATE PROCEDURE CreateNewJobType@job_desc varchar(50), @min_lvl tinyint, @max_lvl tinyintASINSERT INTO jobs (job_desc, min_lvl, max_lvl)VALUES (@job_desc, @min_lvl, @max_lvl)

This stored procedure is called CreateNewJob. It accepts three parameters of various types. It then proceeds to insert a new record into the Jobs table using these arguments.

Visual Basic Code

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objCommand As New ADODB.CommandDim Params(1 To 3) As Variant' Objects to be used in this operation -' objConn, the basic connection' objCommand, which handles the stored procedure' Params(1 To 3), an array to hold the argumentsWith 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 = "CreateNewJobType".ActiveConnection = objConnEnd With' Tell objCommand what it will be working withParams(1) = InputBox("Enter the job description (varchar(50)):")Params(2) = InputBox("Enter the minimum level (tinyint):")Params(3) = InputBox("Enter the maximum level (tinyint):")' Grab the arguments from the userobjCommand.Execute , Params' Execute the stored procedure, passing it the parameterSet objCommand = NothingobjConn.CloseSet objConn = Nothing' Close all references    End Sub

Page 8 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.

Thanks for your registration, follow us on our social networks to keep up-to-date