Create a New SP with Informational Headers & TRY/CATCH Block
AndrewJacksonZA
Posts: 19 Bronze 2
Snippet: csp
Description: Create a new stored procedure with informational headers and a TRY/CATCH block.
Code:
Use CTLR+SHIFT+M to bring up a dialog box that let's you enter <these, values> quickly and easily.
Please see my blog entry "A Way to Handle Raised Errors in Both Dynamic and Static SQL Code" for more information about [dba_Error_Handler].
Description: Create a new stored procedure with informational headers and a TRY/CATCH block.
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: A. Jackson
-- Create date: 2010/<Create Month,,12>/<Create Day,,31>
-- Description: <Description,,>
--
-- Modifications:
-- Author:
-- Date: YYYY/MM/DD
-- Description:
-- :
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- WITH ENCRYPTION, RECOMPILE, EXECUTE AS CALLER|SELF|OWNER| 'user_name'
-- @parameter_name AS scalar_data_type ( = default_value ), ...
AS
BEGIN
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
$CURSOR$
END TRY
BEGIN CATCH
EXEC [dbo].[dba_Error_Handler] @isDynamicSQL = <Is this using Dynamic SQL?,bit,0>
END CATCH
END
GO
Use CTLR+SHIFT+M to bring up a dialog box that let's you enter <these, values> quickly and easily.
Please see my blog entry "A Way to Handle Raised Errors in Both Dynamic and Static SQL Code" for more information about [dba_Error_Handler].
