October 2, 2013 at 12:10 pm
Hi Guys,
I have quick question, I am using SP, in the SP I am using few Insert and Update statements.
I want to know what should I do, If one Insert or Update Statement fails SP gonna give me error, however nothing happen (Not any insert or any update) any advise would be great appreciate...
Urgent Please.
Thank You,
October 2, 2013 at 12:41 pm
What's your problem? I don't understand what you need.
October 2, 2013 at 12:50 pm
My question was, How I can you RollBack Sql Syntax, Here is a E.G
Alter MYSP
Begin Tran
1st Insert Statement
-- Rollback the transaction if there were any errors Start
IF @@ERROR <> 0
BEGIN
ROLLBACK
END
-- Rollback the transaction if there were any errors End
2nd Insert Statement
-- Rollback the transaction if there were any errors Start
IF @@ERROR <> 0
BEGIN
ROLLBACK
END
-- Rollback the transaction if there were any errors End
1st Update Statement
-- Rollback the transaction if there were any errors Start
IF @@ERROR <> 0
BEGIN
ROLLBACK
END
-- Rollback the transaction if there were any errors End
COMMIT
Note:- Is this thing work if any SQL Syntax fail it can roll back, Am i right?
October 2, 2013 at 12:59 pm
You should better use TRY...CATCH which is available since 2005
October 2, 2013 at 12:59 pm
Since you are posting in the 2008 forum I will assume you are using 2008 or later. Don't use @@error, it is more difficult to work with and the code you posted is nowhere close. You should instead use try/catch.
Alter MYSP
Begin transaction
Begin Try
1st Insert Statement
2nd Insert Statement
1st Update Statement
commit transaction
end try
begin catch
rollback transaction
--Do something here so you know it failed
end catch
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2013 at 1:14 pm
Is this looks good to you?
Alter MYSP
Begin transaction
Begin Try
1st Insert Statement
2nd Insert Statement
1st Update Statement
commit transaction
end try
begin catch
rollback transaction
-- Raise an error and return
RAISERROR ('Error in SP.', 16, 1)
RETURN
end catch
October 2, 2013 at 1:27 pm
rocky_498 (10/2/2013)
Is this looks good to you?Alter MYSP
Begin transaction
Begin Try
1st Insert Statement
2nd Insert Statement
1st Update Statement
commit transaction
end try
begin catch
rollback transaction
-- Raise an error and return
RAISERROR ('Error in SP.', 16, 1)
RETURN
end catch
That is pretty much exactly what I posted so I would have to say that yes it looks good to me. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2013 at 2:15 pm
Yes 98% same except
-- Raise an error and return
RAISERROR ('Error in SP.', 16, 1)
RETURN
Thank You For your help!
October 2, 2013 at 2:19 pm
rocky_498 (10/2/2013)
Yes 98% same except-- Raise an error and return
RAISERROR ('Error in SP.', 16, 1)
RETURN
Thank You For your help!
You are welcome. You really don't need the RETURN in there as it is likely at the end of your procedure anyway. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply