随笔- 93 文章- 1 评论- 98 阅读- 18万

存储过程模板(StoredProc+Template)

100

101

102

103

create/alter procedure <SP Name>
 
/*---------------------------------------------------------------------------
DESCRIPTION:
This procedure will get the list of users.
 
AUTHOR: 
DATE:
 
PARAMETERS:
Parameter 1: Description of parameter1 
Parameter 2: Description of parameter2
 
RETURN VALUE:
 
AFFECTED TABLES:
Table1
Table2
---------------------------------------------------------------------------*/
 
@DiscussionID uniqueidentifier
...
as
 
begin
 
      -- other declare statements
 
      declare @Error int
 
      declare @TrCount tinyint
 
      -- other set statements
 
      set @Error = 0
 
      set @TrCount = @@trancount
 
      -- other core/main logic Example1...
 
      select @Error = @@error, @RowCount = @@rowcount
 
      if (@Error <> 0) goto error
 
      -- other core/main logic Example 2...
 
      if (@RowCount = 0) 
 
            begin set @Error = <some sysmessage error#> raiserror (@Error, 16, 1) goto error end
 
      -- other core/main logic Example 3...
 
      if @Error <> 0 
 
            begin raiserror(@Error, 16, 1) goto error end
 
      -- should be last statements after processing all other logic
 
      if @@trancount > @TrCount 
 
      commit transaction
 
      goto conclude
 
      -- other logic...
 
      error:
 
            if @@trancount > @TrCount 
 
                  rollback transaction
 
      conclude:
 
            -- anything that needs to be undone like cleanup, drop temp tables, etc.
 
            -- set flags need to be reset, for example 'set nocount off'
 
            -- prepare output parameters if any
 
            -- other xml output for return if any, for example ‘select @Error RetCode for xml raw’
 
      return @Error
 
end
  
go