Wednesday, February 25, 2009

Exception Handling TRY-CATCH T-SQL


CREATE PROCEDURE UpdateClient
(
@ClientID int,
@Name varchar(50)

)
AS
BEGIN

declare @someID int

BEGIN TRY
BEGIN TRAN

SET @Name = NULLIF(@Name, '')

INSERT INTO ...
VALUES ...

SELECT @someID = scope_identity()

UPDATE ...
SET SomeID = @someID
WHERE ClientID = @ClientID

COMMIT TRAN
END TRY

BEGIN CATCH
ROLLBACK TRAN
EXEC ThrowException

END CATCH
END
GO

/*
*/

CREATE PROCEDURE ThrowException
AS
DECLARE @errorMessage nvarchar(4000),
@errorSeverity int

SELECT @errorMessage = ERROR_MESSAGE(),
@errorSeverity = ERROR_SEVERITY()

RAISERROR(@errorMessage, @errorSeverity, 1)
GO

Tuesday, February 24, 2009

DECLARE variables

in a stored procedure

declare @errorMessage nvarchar(4000),
@errorSeverity int


DECLARE

@ErrorMessage nvarchar(4000),
@ErrorNumber int,
@ErrorSeverity int,
@ErrorState int,
@ErrorLine int,
@ErrorProcedure nvarchar(200)