dcsimg
July 23, 2018
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 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.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

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