In SQL Server Query Analyzer, type something like this:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- The Best Visual Basic Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.