Create a New SP with Informational Headers & TRY/CATCH Block

Share your SQL code snippets for SQL Prompt!

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson, Paul Stephenson

Create a New SP with Informational Headers & TRY/CATCH Block

Postby AndrewJacksonZA » Wed Nov 03, 2010 10:59 am

Snippet: csp
Description: Create a new stored procedure with informational headers and a TRY/CATCH block.
Code:
Code: Select all
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].
AndrewJacksonZA
 
Posts: 17
Joined: Wed May 05, 2010 11:09 am
Location: Johannesburg, South Africa

Return to SQL Prompt code snippets

Who is online

Users browsing this forum: No registered users and 0 guests

cron