March 12, 2010 at 3:43 pm
I use "IF NOT EXISTS" to check for the existence of a record. If none exists, I want to insert records into 3 tables. How do I indicate the end of the routine? In other words: IF NOT EXISTS(SELECT ID FROM Table1 WHERE
ID=2)
Insert Into Table2 ...
Insert Into Table3 ...
Insert Into Table4 ...
From here I want to continue with updates to tables based on a passed in ID. But what syntax do I need to indicate this is the end of the "IF NOT EXISTS" clause and to continue with updates?
Thanks,
Sam
March 12, 2010 at 3:47 pm
IF NOT EXISTS(SELECT ID FROM Table1 WHERE ID=2)
BEGIN
Insert Into Table2 ...
Insert Into Table3 ...
Insert Into Table4 ...
END
CEWII
March 12, 2010 at 9:28 pm
smknox (3/12/2010)
IF NOT EXISTS(SELECT ID FROM Table1 WHERE ID=2)Insert Into Table2 ...
Insert Into Table3 ...
Insert Into Table4 ...
From here I want to continue with updates to tables based on a passed in ID. But what syntax do I need to indicate this is the end of the "IF NOT EXISTS" clause and to continue with updates?
If you are thinking that all three Inserts would happen based on your clause you are wrong. Your clause has ended after the first Insert itself. You have to use a BEGIN...END to ensure all three Inserts happen based on your IF Clause.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 13, 2010 at 3:24 am
BEGIN TRY
BEGIN TRANSACTION;
SAVE TRANSACTION [DoInserts];
IF NOT EXISTS
(
SELECT *
FROM dbo.Table1 WITH (UPDLOCK, HOLDLOCK)
WHERE ID = 2
)
BEGIN
INSERT dbo.Table1 ...
INSERT dbo.Table2 ...
INSERT dbo.Table3 ...
INSERT dbo.Table4 ...
END;
COMMIT TRANSACTION;
END TRY;
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK TRANSACTION;
IF XACT_STATE() = 1
BEGIN
ROLLBACK TRANSACTION [DoInserts];
COMMIT TRANSACTION;
END;
-- Rethrow error or other handling here
END CATCH;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 6:58 am
Big thanks! Thanks for the additional error trapping as well.
Sam
March 13, 2010 at 1:05 pm
Paul:
I assumed the code should encapsulate the updates as well, right? :
BEGIN TRY
BEGIN TRANSACTION;
SAVE TRANSACTION [DoUpdates];
BEGIN
Update...
Update..
END
COMMIT TRANSACTION;
END TRY;
CATCH code the same with [DoUpdates]
Thanks for the help.
March 13, 2010 at 7:48 pm
Yes, if you want them to all succeed or fail as a unit.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply