Solved

nested stored procedures

Posted on 2006-06-29
3
223 Views
Last Modified: 2012-06-22
Hi

If you have a stored procedure that sets @@error and you call this procedure from inside another stored procedure, can the outer SP see the @@error set by inner procedure?

Similarly, if a nested SP calls RAISERROR, is there anyway the out SP can see the custom error message or message ID set by raise error

thanks a lot
andrea
0
Comment
Question by:andieje
3 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
Comment Utility
You need to use an output variable to pass the Error value from the inner sp

CREATE procedure innerSp @error int output
as
declare @b tinyint
if 1 =1
 
  set @b = 255+1
  SELECT @error = @@ERROR

GO
CREATE procedure mainSp
AS
  declare @error int
  exec innersp @error output
  select @@Error ,@error
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 250 total points
Comment Utility
> If you have a stored procedure that sets @@error

You mean, @@error is set by producing an error in the stored procedure. You can't type SET @@ERROR=1

> can the outer SP see the @@error set by inner procedure?

yes:

create table t1(i int not null primary key)
insert into t1 select 1
go

create procedure a as insert into t1 select 1
go

exec a
print @@error

===


prints 2627.

> Similarly, if a nested SP calls RAISERROR, is there anyway the out SP can see the custom error message or message ID set by raise error

message - no, message id - yes - the same @@error shows it.


0
 

Author Comment

by:andieje
Comment Utility
thanks very much
andrea
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now