sql query having try ,catch and finally with rollback

sourabh choubey

8y3k

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
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
sourabh choubey8y ago
Can you please explain what are the use of

SET ANSI_NULLS ON
QUOTED_IDENTIFIER
mode
nocount

and where is finally block?
Dharmraj Thakur8y ago
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