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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top