try and catch equivalent in tsql
-
Im trying todo a insert from a previous query. Sometimes the insert will pop an error on a duplicate. I need to have the loop continue. Is their a way to get the sql statemtent to continue on error?
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
INSERT INTO MyDb.dbo.tbl_newspro_Subscribers (SiteID, Email, Name, DeliveryFormat, OptInMethod) VALUES (@SiteID, @Email, @Email, 'HTML', 'Batch Insert')
Set @user-id = @@IDENT_CURRENT
INSERT INTO MyDb.dbo.tbl_newspro_SubscriberLists (SubscriberID, NewsLetterListID) VALUES (@UserID, @ListID)
COMMIT TRAN
FETCH NEXT FROM transfer_cursor INTO @Email,@SiteID,@ListID,@LastName
END
Edited by - gabe@getfused.com on 10/13/2003 1:25:09 PM
Gabriel L Smallman -
While there are some errors you can check for by accessing the @@ERROR variable, there are some that cannot be trapped. For these types of errors, you will want to code your own verification:
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS ( SELECT * FROM tbl_newsPro_Subscribers WHERE <values for uniqueness> )
BEGIN
BEGIN TRAN
Insert
Insert
COMMIT TRAN
END
FETCH NEXT
END
Guarddata-
-
Duplicates can be checked for using @@error, just remember, if you are calling into SQL using a client, e.g. java, each error you ignore is going to be passed to the client (as a SQLException in java's case) regardless of the fact that execution may have been successful.
guarddata's solution is best, but I have found situations when this isn't practical.
For example, you may have multiple foreign keys, and checking for violations of each one may be more mission than its worth.
Thankfully the next version of SQL Server will include support for .Net, which means we have try catch
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply