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









MS Sql Server LazyDBA home page