Stored Procedure Return values

[scabral79]

Hi,

i'd like to create a stored procedure in SQL 2000 that runs some insert statements (let's say 3 of them). This stored procedure will actually be called from another stored procedure from another database.

What i would like to have happen is if any of the insert statements fail for some reason, i would like to stop the execution of the stored procedure and rollback any transactions and return a value of 1 to the calling stored procedure.

if all the statements are successfull i want to commit the transactions and return a 0 back to the calling procedure.

Does anyone have an example of what this syntax would look like?

i've never used return or output variables before so i'm not too familiar with them.

thanks
Scott

 

[chigley]

Right, lets get down to some syntax

Output variables example

CREATE Procedure myProc (@outputVar INT OUTPUT)
AS
BEGIN
SET @outputVar = 1

return 0 --I think every proc needs a return value not matter what
END

Return variables example

CREATE Procedure myProc ()
AS
BEGIN
RETURN 1
END

The syntax for a transaction is

BEGIN TRAN TranName

--Do first insert
IF @error <>0
BEGIN
ROLLBACK TranName
return 0
END

--Do second insert
IF @error <>0
BEGIN
ROLLBACK TranName
return 0
END

--Do third insert
IF @error <>0
BEGIN
ROLLBACK TranName
return 0
END

COMMIT TranName
return 1
END