March 01, 2012

What the customer Needed


 The first rule of kiss ( Keep it Simple...)

The Request
Please Delete Record X from table Y
where ID = Z


KISS



How it was code:
Declare @SQLInstance varchar(255)
Declare @Message varchar (255)

set @Message = 'Invalid Instance'
set @SQLInstance = 'SQLInstanceName'


BEGIN TRY

If (@@servername <> @SQLInstance)
    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.
    RAISERROR (@message, -- Message text.
               16, -- Severity.
               1 -- State.
               );
BEGIN TRAN
    DELETE F
    FROM
        TABLENAME F
    WHERE
        ID = 5

IF @@TRANCOUNT > 0
 COMMIT
   
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
   IF @@TRANCOUNT > 0
    ROLLBACK

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

What the customer needed. 
Begin Tran
Delete From TableName 
where Id = 5
If @@error <> 0 and @@trancount>0
 rollback 
else
commit

No comments:

Post a Comment

Contact Form

Name

Email *

Message *