Checking return codes goes something like this


Declare @ret_code int
set @ret_code = 0

exec @ret_code = sp_my_stored_proc

If @ret_code = 0
begin
SELECT 'Success'
end

Else

Begin

Select 'Proc Failed with return code ' +
convert(varchar(20),@ret_code)
end


Hope this helps

Robert Elmes
Database Administrator
Thales Defence - Communications Division





-----Original Message-----
From: Fang, Elaine [mailto:Elaine.[Email Address Removed] 10 July 2002 17:09
To: [Email Address Removed] How to check the SP return value from calling SQL prorgram?


Hi,

If I call a SP from SQL code, is there a way for me to check the return
value from the SP? I mean, if the SP was executed successfully, I will
expect it to return 0 (zero), otherwise it will return 1 if there is
anything wrong as I coded.

e.g. a code sample might looks like:

declare @ID int, @name varchar(20)
declare StuCursor cursor for select * from old_students

open stucursor
fetch next from StuCuror into @id, @name

while (@@fetch_status <> -1)
being
if (@@fetch_status <> -2)
begin
exec sp_insert_person @id, @name -- this sp will convert ID
and insert the record into person table
-- do I have to check the return value here?
fetch next from StuCursor into @id, @name
end
end

close StuCurosr
Deallocate StuCursor

Questions:
1. What syntax should I use to check the return value from sp_insert_person?
Will @@error work?
2. Where should I put it in above code sample?

thanks a lot!

Elaine










---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page