Ask the Expert

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?

    Requires Free Membership to View

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 first published in April 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: