Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-06 : 08:45:37
|
bala writes "Hello,
I am having a procedure where there are several lines of inserts and updates besides select statements. I want to execute the entire in a batch, So I have put Begin Tran at the begining.
e.g.,
BEGIN TRAN
SELECT col_a, col_b, col_c from table_a
IF(@@ERROR = 0)
begin
INSERT INTO table_b VALUES(SELECT * FROM table_c)
INSERT INTO table_c VALUES(SELECT * FROM table_e)
INSERT INTO table_d VALUES(SELECT * FROM table_f)
end
ELSE
begin
UPDATE table_x SET col_a = 'failed'
RETURN 1
end
INSERT INTO table_g VALUES(SELECT * FROM table_h)
IF(@@ERROR = 0)
COMMIT TRAN
RETURN 2
ELSE
ROLLBACK TRAN
RETURN 3
My probelm is that I want to Return a value upon commit(only when there is no error) and upon rollback(if any error at all). And whenever there is a error I am getting a empty return value in the return parameter of the command object.
Is there a solution where can I get the return value when an error occurs?
Thanks" |
|
|
|
|
|
return value in a stored procedure