Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Return a value from stored procedure to calling application

Reply
Thread Tools

Return a value from stored procedure to calling application

 
 
Paul
Guest
Posts: n/a
 
      01-19-2004
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?
 
Reply With Quote
 
 
 
 
=?Utf-8?B?UmFtaXJvIENhbGRlcm9u?=
Guest
Posts: n/a
 
      01-19-2004
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to detect return value from stored procedure used by a Details =?Utf-8?B?QmlzaG95?= ASP .Net 1 12-24-2006 05:54 PM
stored procedure return value question philip ASP .Net 2 08-10-2006 03:42 PM
Is there a possibility to call a stored procedure on the MSSQL DB without java to wait for a return value? gagu911@gmx.ch Java 3 11-30-2005 03:59 PM
Stored Procedure Return value question Nick ASP .Net 8 02-10-2005 02:32 PM
Stored Procedure has both return value and data set (SqlDataReader) Neo Chou ASP .Net 1 08-03-2003 06:23 AM



Advertisments