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
