By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,691 Members | 1,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,691 IT Pros & Developers. It's quick & easy.

Return a value from stored procedure to calling application

P: n/a
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?
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Paul" <pa********@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...
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?


I can't answer the ASP question, but as regards the stored procedure issues,
here are some useful links:

http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/error-handl....html#SP-check

In general, I would return success or failure (or some other execution
state) with RETURN, and scalar values with an output parameter. Using UDFs
gives other options, as the first link above explains.

Simon
Jul 20 '05 #2

P: n/a
[posted and mailed, please reply in news]

Paul (pa********@hotmail.com) writes:
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.
That depends on how you call the stored procedure, but assuming that
you use ADO that would be something like:

cmd.CommandType = adStoredProc
cmd.CreateParameter("RETURN_VALUE", adInteger, adReturnValue)
cmd.CreateParameter("@P1", ...)

cmd.Execute

ret = cmd.Parameters("RETURN_VALUE")

Note that if you use a server-side cursor, you first need to get all
rows before the return value is availble. This is not an issue with
a client-cursor.

I should add the disclaimer that I don't use ADO daily, so the above
may not be entirely correct. As for ASP, I don't know much about it.
Isn't that a tree? :-)
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.


I echo Simon and recommend using return values for status indicating
success and nothing else. Use output parameters for scalar data.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
Hi Simon,

Thanks for the response and great links
Jul 20 '05 #4

P: n/a
Hi Erland,

Thanks for the response. Much appreciated.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.