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? 4 23461
"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
[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
Hi Simon,
Thanks for the response and great links
Hi Erland,
Thanks for the response. Much appreciated. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: dinesh prasad |
last post by:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my...
|
by: Daniel |
last post by:
hi ng,
i am newbie to sqlserver and my problem seems simple, but i didn't find
information about it:
How can i display the
RETURN @x
value of a stored procedure in the sql analyzer of the...
|
by: Steven |
last post by:
I'm calling a stored procedure which has an output parameter of type int.
Once the stored procedure is executed, I want to check the value of the
parameter in case it is null. However, when the a...
|
by: Rhino |
last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a
Throwable back to the calling program as an OUT parameter?
If yes, what datatype should I use when registering the...
|
by: Rhino |
last post by:
I am trying to verify that I correctly understand something I saw in the DB2
Information Center.
I am running DB2 Personal Edition V8.2.1 on Windows. I came across the
following in the Info...
|
by: Paul |
last post by:
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,...
|
by: philip |
last post by:
hello, i am new to asp.net and sql server, and i have 3 questions for
asking:
1. i am writing a store procedure of login validation for my asp.net
application and wondering what the different...
|
by: Fir5tSight |
last post by:
Hi All,
I have a C#.NET code as follows:
private void ScanInput_KeyPress(object sender,
System.Windows.Forms.KeyPressEventArgs e)
{
try
{
Row lRow = this.Connection.InsertScannedFile(ID);
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |