EXEC in Stored Procedures

[elmo29]

I have to insert 5 rows at a time in a table and each row follows on from the one before it so I need to make sure that there can be no error on updating else it will always roll back. It will be quite common for there to be an error as three of the values in a row have to be checked against other values in another table to check they are valid.

Right! So I have a stored procedure that I can call where I pass in the three values and that checks if they are valid, and uses a return value of 0 if ok. So basically I want my code to work as follows is possible

rollback transaction

delete all rows in question

exec mystoredproc (1,2,3)
if ok insert
else rollback and quit

exec mystoredproc (4,5,6)
if ok insert
else rollback and quit

exec mystoredproc (7,8,9)
if ok insert
else rollback and quit

exec mystoredproc (10,11,12)
if ok insert
else rollback and quit

exec mystoredproc (13,14,15)
if ok insert
else rollback and quit

end of transaction

Its a bit more complicated than that - but you get the gist.

What I would like to know is, is this possible? and how do I check what the return value is form the EXEC statement. I.e.

can I do something like,
if returnvalue = 0 OK else ....

Also I am passing 27 input parameters to the stored procedure .... is this OK - or is there any better way?? I have to ensure that the database is never left in an incorrect state so I kinda figure I have to do it this way

Any help appreciated!!

[sig][/sig]

 

[foxdev]

In your called stored procedure you can use the OUTPUT variable; the result of the variable is what is returned to the calling program's EXEC. In this way, you could determine the result of the called SP. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[elmo29]

Well, I have it working now.

DECLARE @RETURN

EXEC @RETURN = storedprocedure values

if @RETURN <> 0 GOTO ON_ERROR

Simple [sig][/sig]