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

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.

Please create a username to comment.