asked
Transaction Handling in SQL
I have SP like this. I need to know I am doing transaction handling correctly in this SP. And I want to give meaningful message from Program if transaction roll back. How can I do that. Plase support me. ALTER PROCEDURE [dbo].[SP_Email_SaveEmailData] -- Add the parameters for the stored procedure here @FromID INT, @ToIdsClient VARCHAR(MAX), @CcIdsClient VARCHAR(MAX), @BccIdsClient VARCHAR(MAX), @Subject VARCHAR(250), @Body VARCHAR(MAX), @IsSend BIT, @StatusID INT, @CurrentDate DATETIME, @CurrentEmailID INT OUTPUT, @REPHOST VARCHAR(20), @ToIdsEmp VARCHAR(MAX), @CcIdsEmp VARCHAR(MAX), @BccIdsEmp VARCHAR(MAX), @CurrentUser INT, @FromNames VARCHAR(MAX), @ToNames VARCHAR(MAX), @CcNames VARCHAR(MAX), @AttachingClients VARCHAR(MAX), @AttachingEmployees VARCHAR(MAX), @UID INT AS BEGIN IF(@IsSend = 1) BEGIN BEGIN TRY BEGIN TRANSACTION INSERT INTO tbl_EmailDetails (EmployeeID,Subject,Body,Date,IsSend,StatusID,REPHOST,MailBeeUID) VALUES (@FromID,@Subject,@Body,@CurrentDate,@IsSend,@StatusID,@REPHOST,@UID) SET @CurrentEmailID = SCOPE_IDENTITY() IF(@ToIdsClient <> '') PRINT 'TO CLIENT' BEGIN DECLARE @CurrentToID INT DECLARE CurserToC CURSOR FOR SELECT * FROM dbo.fn_Splitter(@ToIdsClient) OPEN CurserTo FETCH NEXT FROM CurserToC INTO @CurrentToID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) VALUES (@CurrentEmailID,@CurrentToID,'To',@IsSend,@REPHOST,0,@CurrentUser,@CurrentDate) FETCH NEXT FROM CurserToC INTO @CurrentToID END CLOSE CurserToC DEALLOCATE CurserToC END IF(@CcIdsClient <> '') PRINT 'CC CLIENT' BEGIN DECLARE @CurrentCcID INT DECLARE CurserCcC CURSOR FOR SELECT * FROM dbo.fn_Splitter(@CcIdsClient) OPEN CurserCcC FETCH NEXT FROM CurserCc INTO @CurrentCcID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) VALUES (@CurrentEmailID,@CurrentCcID,'Cc',@IsSend,@REPHOST,0,@CurrentUser,@CurrentDate) FETCH NEXT FROM CurserCcC INTO @CurrentCcID END CLOSE CurserCcC DEALLOCATE CurserCcC END IF(@BccIdsClient <> '') PRINT 'BCC CLIENT' BEGIN DECLARE @CurrentBccID INT DECLARE CurserBccC CURSOR FOR SELECT * FROM dbo.fn_Splitter(@BccIdsClient) OPEN CurserBcc FETCH NEXT FROM CurserBccC INTO @CurrentBccID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) VALUES (@CurrentEmailID,@CurrentBccID,'Bcc',@IsSend,@REPHOST,0,@CurrentUser,@CurrentDate) FETCH NEXT FROM CurserBccC INTO @CurrentBccID END CLOSE CurserBccC DEALLOCATE CurserBccC END IF(@ToIdsEmp <> '') BEGIN DECLARE @CurrentToIDEmp INT DECLARE CurserToE CURSOR FOR SELECT * FROM dbo.fn_Splitter(@ToIdsEmp) OPEN CurserToE FETCH NEXT FROM CurserToE INTO @CurrentToIDEmp WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) VALUES (@CurrentEmailID,@CurrentToIDEmp,'To',@IsSend,@REPHOST,1,@CurrentUser,@CurrentDate) FETCH NEXT FROM CurserToE INTO @CurrentToIDEmp END CLOSE CurserToE DEALLOCATE CurserToE END IF(@CcIdsEmp <> '') PRINT 'CC EMP' BEGIN DECLARE @CurrentCcIDEmp INT DECLARE CurserCcE CURSOR FOR SELECT * FROM dbo.fn_Splitter(@CcIdsEmp) OPEN CurserCcE FETCH NEXT FROM CurserCcE INTO @CurrentCcIDEmp WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) VALUES (@CurrentEmailID,@CurrentCcIDEmp,'Cc',@IsSend,@REPHOST,1,@CurrentUser,@CurrentDate) FETCH NEXT FROM CurserCcE INTO @CurrentCcIDEmp END CLOSE CurserCcE DEALLOCATE CurserCcE END IF(@BccIdsEmp <> '') PRINT 'BCC EMP' BEGIN DECLARE @CurrentBccIDEmp INT DECLARE CurserBccE CURSOR FOR SELECT * FROM dbo.fn_Splitter(@BccIdsEmp) OPEN CurserBccE FETCH NEXT FROM CurserBccE INTO @CurrentBccIDEmp WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) VALUES (@CurrentEmailID,@CurrentBccIDEmp,'Bcc',@IsSend,@REPHOST,1,@CurrentUser,@CurrentDate) FETCH NEXT FROM CurserBccE INTO @CurrentBccIDEmp END CLOSE CurserBccE DEALLOCATE CurserBccE END COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH END
The transaction handling seems correct. You can raise a friendly error to the application via RAISERROR [
http://msdn.microsoft.com/en-us/library/ms178592.aspx][1] Some common information to return is this: ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; from here: [
http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx][2] [1]:
http://msdn.microsoft.com/en-us/library/ms178592.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx
You don't specify what version of SQL Server, but with 2012 you can also use 'THROW' instead of RAISERROR. ;-)
http://msdn.microsoft.com/en-us/library/ee677615.aspx
While your logic might be solid in concern with the transaction handling it would be best if you can avoid using cursors in your code. If this is to be deployed on a busy system then this is going to become a bottleneck very quickly.
great point. The first cursor could be replaced with something like this (just to get the ball rolling): INSERT INTO dbo.tbl_EmailSentTo(EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) SELECT @CurrentEmailID, ColumnNameFromFN_Splitter, 'To', @IsSend, @REPHOST, @CurrentUser, @CurrentDate FROM dbo.fn_Splitter(@ToIdsClient)
Thoroughly agreed. There are a few things in SQL that have to be done iteratively, but they are few. The rest of the time it is best to work with sets and avoid cursors.
When you explicitly loop, you are forcing SQL Server to deal with multiple single row sets rather than a single multi-row set. "In general" this seems to be something for which SQL isn't well optimized and that is often reflected in poor cursor performance relative to an equivalent set based approach.
I used your query like this. INSERT INTO dbo.tbl_EmailSentTo (EmailID,ClientID,Field,IsSend,REPHOST,IsEmployee,SavedEmployee,Date) SELECT @CurrentEmailID, ID from dbo.fn_Splitter, 'To', @IsSend, @REPHOST, @CurrentUser, @CurrentDate FROM dbo.fn_Splitter(@ToIdsClient) But this is giving following error. Msg 102, Level 15, State 1, Procedure SP_Email_SaveEmailData, Line 58 Incorrect syntax near 'To'.
Yeah... That's working correctly.. Thank u KenJ.. But another one que.. What is the advantage when I'm using like this?
17 People are following this question.
