EXECUTE @return_status = stored_proc

See EXECUTE in Books Online for more information.

> -----Original Message-----
> From: Fang, Elaine [SMTP:Elaine.[Email Address Removed] Sent: Wednesday, July 10, 2002 5:09 PM
> To: [Email Address Removed] Subject: 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] For additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page