Problem solve Get help with specific problems with your technologies, process and projects.

How can I create a stored procedure in SQL Server and call the procedure in VB?

How can I create a stored procedure in SQL Server and call the procedure in VB?

In SQL Server Query Analyzer, type something like this:

This procedure checks for the existence of a customer and tries to delete it; it will return "0" if it fails or "1" if it is succesful.

 CREATE PROCEDURE Customer_Remove @vLastName varchar(30), @vCount int OUTPUT AS DECLARE @vCustomerID = CustomerID FROM Customers WHERE LastName = @vLastName IF @vCustomerID = IS NULL Return (0) ELSE BEGIN DELETE FROM Customers WHERE CustomerID = @vCustomerID SELECT @vCount = COUNT(*) FROM Customers Return (1) END END IF

In VB, this sample code shows how to call the Customer_Remove stored procedure and access both the return code and the vCount output parameter after the call:

 Dim conn As ADODB.Connection, cmd As ADODB.Command Dim prm As ADODB.Parameter Set conn = New ADODB.Connection Set cmd = NEW ADODB.Command conn.ConnectionString = "DSN=xDSN" conn.Open Set cmd.ActiveConnection = conn cmd.CommandText = "Customer_Remove" Set prm = cmdCreateParameter("Return", adInteger, adParamReturnValue, ,0) cmd.Parameters.Append prm Set prm = cmdCreateParameter("@vLastName", adVarChar, adParamInput, _ 30, "Cano") cmd.Parameters.Append prm Set prm = cmdCreateParameter("@vCount", adInteger, adParamOutput, ,0) cmd.Parameters.Append prm cmd.Execute Debug.Print cmd("Return") "Return Code Debug.Print cmd("@vCount") "Count


For More Information

This was last published in April 2001

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.