sql query having try catch and finally with rollback

Question

Any one expert in writting stored procedure sql query while insert update and delete record i want t use try catch and finally block with rollback transaction and commit sql statement . Please send me the struectur by an example

sourabh choubey · Answer

Can you please explain what are the use of SET ANSI_NULLS ON QUOTED_IDENTIFIER mode nocount and where is finally block?

Dharmraj Thakur · Answer

Here is the structure you are looking for.... SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create proc [dbo].[usp_Products] @ReturnVal INT = 0 OUT  @Mode int =0 @ProductId int =0 @CategoryId int =0 AS BEGIN SET NOCOUNT ON ; DECLARE @StartTime as datetime DECLARE @Endtime as datetime SET @StartTime = GETDATE(); BEGIN TRY BEGIN TRAN -- INSERT IF @Mode = 1 BEGIN INSERT INTO Table1 ( ProductId CategoryId ) VALUES ( @ProductId @CategoryId ) SELECT @ReturnVal = @@IDENTITY; END --UPDATE IF @Mode = 2 BEGIN UPDATE Table1 SET ProductId=@ProductId CategoryId=@CategoryId WHERE ProductId=@ProductId; END --Delete IF @Mode = 3 BEGIN UPDATE Table1 SET Deleted = 1 WHERE ProductId = @ProductId; END COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRAN SELECT @@ERROR END ;THROW END CATCH SET @Endtime = GETDATE() END

Dharmraj Thakur · Answer

SET ANSI_NULLS ON : This option specifies the setting for ANSI NULL comparisons. When this is on any query that compares a value with a null returns a 0. When off any query that compares a value with a null returns a null value. QUOTED IDENTIFIER ON/OFF: This options specifies the setting for usage of double quotation. When this is on double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words. Mode: This is a just a parameter to identiy what to do like insert update or delete... You put more modes in same stored procedure. SET NOCOUNT ON; When SET NOCOUNT is ON the count is not returned. When SET NOCOUNT is OFF the count is returned. I used to think that 9 row(s) affected was returned from server in plain text but it doesn't seem to be the case. It's in fact a small message packet called DONE_IN_PROC. Finally block: https://stackoverflow.com/questions/13054101/finally-clause-in-sql-server-transaction-something-that-will-execute-irrespecti

sql query having try ,catch and finally with rollback