Return a value from stored procedure to calling application

[Paul]

Hi,

In SQL Books Online in the section on @@Error it gives the following
example:

-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)

-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GO

How do I access the value returned by the RETURN statement (i.e. 99 or
0) in my asp application that called the stored proc.

Sometimes rather than just return an integer signifying success or
failure I've seen examples where the id of the newly added item is
returned on success and perhaps -1 if the operation fails. These
examples make use of ouput parameters to achieve this. If the
operation succeeds then then the output parameters value is set to the
new id and this is accessed from the calling application.

E.g.

IF @@ERROR <> 0
BEGIN
-- Return -1 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
SELECT @MyOuptputParameter = -1
END
ELSE
BEGIN
-- Return id to the calling program to indicate success.
PRINT "The new author information has been loaded"
SELECT @MyOuptputParameter = @@IDENTITY
END

Why go to this trouble if you can use the RETURN statement?

 

[Guest]

Hi,

If you want to get the return value in your .NET application, all you have to do is to add a Parameter in the SqlCommand's Parameters collection. This SqlParameter object must have the ParameterDirection property set to ParameterDirection.ReturnValue.
After executing the command, you can access the parameter like an output parameter:

Hope this helps,

Ramiro Calderon