一个存储过程的 模板

BEGIN TRY
SET NOCOUNT ON
----------------------------------------------------------------------------------
-- Declarations
----------------------------------------------------------------------------------
DECLARE
    @Rows               int
   ,@RetCode            int
   ,@ExitStatus         int
   ,@Msg                nvarchar(2100)
   ,@ProcedureName      sysname
   ,@DBName             sysname
   ,@CreatedBy          sysname
   ,@tmpTableName       sysname

 
----------------------------------------------------------------------------------
-- Initializations
----------------------------------------------------------------------------------
SELECT @ProcedureName = OBJECT_NAME(@@procid)
      ,@ExitStatus          = 1001     -- Error ID for error on importing data

EXEC   dbo.sysLogMsg @ProcedureName = @ProcedureName, @Msg = 'BEGIN Procedure'

-------------------------------------------------------------------------------
-- Remove ALL rows from IPTrademark
-------------------------------------------------------------------------------

SELECT @Msg = 'TRUNCATE TABLE dbo.DimTrademarkDetail'
EXEC   dbo.sysLogMsg @ProcedureName = @ProcedureName, @Msg = @Msg, @IndentLevel = 1

TRUNCATE TABLE XXXXX

-------------------------------------------------------------------------------
-- Create Initial IPTrademark
-------------------------------------------------------------------------------

-- Add New IPTrademark Records
SET @Msg = 'Insert NEW data into dbo.DimTrademarkDetail'

;--******************
---Logic
--******************

SELECT @rows = @@rowcount
EXEC   dbo.sysLogMsg @ProcedureName = @ProcedureName, @Msg = @Msg, @Rows = @Rows, @IndentLevel = 1

--------------------------------------------------------------------------------
-- Exit
--------------------------------------------------------------------------------
    --SET @Msg = 'Drop tmp tables'
      
    EXEC   dbo.sysLogMsg @ProcedureName = @ProcedureName, @Msg = 'END Procedure'
    RETURN (0)
END TRY

--------------------------------------------------------------------------------
-- Exit with error
--------------------------------------------------------------------------------
BEGIN CATCH
   SET @Msg = 'ERROR Failed to ' + @Msg
   SET @DBName = DB_NAME()
   SET @CreatedBy = SUSER_SNAME()

   SET @Msg = @Msg                                                                      + CHAR(10) +
         ' DBName        = ' + @DBName                                                  + CHAR(10) +
         ' Procedure     = ' + OBJECT_NAME(@@PROCID)                                    + CHAR(10) +
         ' ErrorNumber   = ' + LTRIM(CONVERT(VARCHAR(9),Error_Number()))                + CHAR(10) +
         ' ErrorState    = ' + LTRIM(CONVERT(VARCHAR(3),Error_State()))                 + CHAR(10) +
         ' ErrorSeverity = ' + LTRIM(CONVERT(VARCHAR(3),Error_Severity()))              + CHAR(10) +
         ' LineNumber    = ' + LTRIM(CONVERT(VARCHAR(9),Error_Line()))                  + CHAR(10) +
         ' ErrorMessage  = ' + LTRIM(CONVERT(VARCHAR(2047),LEFT(Error_Message(),2044))) + CHAR(10) +
         ' ErrorDT       = ' + CONVERT(VARCHAR(23),GETDATE(),121)                       + CHAR(10) +
         ' ErrorBy       = ' + @CreatedBy
  
   EXEC   dbo.sysLogMsg @ProcedureName = @ProcedureName, @Msg = @Msg

   EXEC   dbo.sysLogMsg @ProcedureName = @ProcedureName, @Msg = 'END Procedure'
   RETURN (@ExitStatus)

END CATCH

 

 

 

--SP:sysLogMsg

CREATE PROCEDURE [dbo].[sysLogMsg]
  @ProcedureName SYSNAME
 ,@Msg NVARCHAR(MAX)
 ,@Rows INT = NULL
 ,@IndentLevel INT = 0
AS
BEGIN TRY
SET NOCOUNT ON

-- Local variable declarations
DECLARE   
    @ExitStatus                     INT = 1
   ,@RowMsg                         NVARCHAR(MAX) = ''
   ,@OutMsg                         NVARCHAR(2100)
   ,@NestLevel                      INT

--------------------------------------------------------------------------------
-- Main
--------------------------------------------------------------------------------
-- If the optional @Rows parameter is provided, append the rows affected to the output msg
IF @Rows IS NOT NULL
BEGIN
    SELECT @RowMsg = ': ' + CONVERT(VARCHAR(11), @Rows) + ' rows affected'
END

-- Prepare the output msg
-- Indent the output msg by the number of levels requested (the default is 0)
-- Use the stored procedure @@NESTLEVEL so that we can indent the msg appropriately to show the nesting
-- of called procedures in our output msg
-- sysLogMsg will be @@NESTLEVEL 1 when called from a SQL Script and @@NESTLEVEL 2 when called from another procedure
SET @NestLevel = CASE WHEN @@NESTLEVEL > 2 THEN @@NESTLEVEL - 2 ELSE 0 END + ISNULL(@IndentLevel, 0)
SELECT @OutMsg = REPLICATE('   ', @NestLevel) + CONVERT(VARCHAR(22), GETDATE(), 113) + ' : Procedure ' + @ProcedureName + ' : ' +  @Msg + @RowMsg + ';'

RAISERROR (@OutMsg, 0, 1) WITH NOWAIT

--------------------------------------------------------------------------------
-- Exit
--------------------------------------------------------------------------------
   RETURN (0)
END TRY

--------------------------------------------------------------------------------
-- Exit with error
--------------------------------------------------------------------------------
BEGIN CATCH
   -- There is no point in handling a logging error
   RETURN (@ExitStatus)

END CATCH