Syntax for creating Stored Procedure With Try-Catch

Sourabh07
Posted by Sourabh07 under Sql Server category on | Points: 40 | Views : 1139

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END TRY
BEGIN CATCH
if @@TRANCOUNT>0
Rollback
Declare @err_msg varchar(max),@err_severity int
Select @err_msg = Error_Message(),@err_severity = Error_Severity()
RAISERROR(@err_msg,@err_severity,1)
END CATCH
END
GO

Comments or Responses

Login to post response