存储过程开发模板

总结的一个存储过程开发模板，主要是避免一些常见问题。
书写规范为：SQL关键字均使用小写。但是定义连接属性，如SET NOCOUNT ON可以用大写；变量采用Camel风格，单词首字符大写。

/************************************************ 作者： XXX 创建日期：YYYY-MM-DD 功能描述：（清楚、详尽。如：本存储过程主要用于生成主键ID， 为了适应分布式数据库的应用，采用SiteID（三位）+YYYYMMDD（八位）+顺序位（八位）组成BigInt类型编码。） ----------------------------------------------- 修改者： 修改日期： 修改描述：（如：本次修改加入了对存储过程的错误捕获。） ----------------------------------------------- 传入参数： @X1 数据类型： 描述： @X2 数据类型： 描述： 传出参数： @X1 数据类型： 描述： 返回值： @XX 数据类型： 描述： **********************************************/ -- 先检查存储过程是否存在，如果存在，先drop掉 if Object_Id(N'[dbo].[P_xxxx]', N'P') is not null begin drop procedure [dbo].[P_xxxx] end go create procedure [dbo].[P_xxxx] @xxx1 DataType, @xxx2 DataType output as begin -- 出于性能考虑，这是每个存储过程的第一条语句 -- 当SET NOCOUNT为ON时，将不向客户端发送存储过程中每个语句的DONE_IN_PROC消息。 -- 如果存储过程中包含的一些语句并不返回许多实际数据，则该设置由于大量减少了网络流量，因此可显著提高性能。 SET NOCOUNT ON -- 定义错误变量，为raiserror使用 declare @ErrorMessage nvarchar(4000); declare @ErrorSeverity int; declare @ErrorState int; -- 注释： ---- 1、为方便调试，在存储过程内部一律使用“--”代替“/* */” ---- 2、每个关键性操作请在前面注释 -- 变量定义： ---- 1、不要在循环中定义变量 ---- 2、如果变量是用于存储某个字段的值，请使变量类型（包括精度）和字段类型一致 ---- 3、最好显示的为变量初始化 -- 变量赋值： ---- 使用set @xxx = ?，不要用早期版本的select @xxx = ?方式 ---- 从SQL中为变量赋值采用 select @xxx1 = col1, @xxx2 = col2 from tabelname -- 一次性清空表，请使用truncate代替delete truncate table tablename -- insert语句要把字段名写全 insert into tablename(col1, col2…) values(@xxx1, @xxx2…); -- 批量插入 insert into tablename1(col1, col2…) select col1, col2… from tablename2 …… -- 判断语句 if (@xxx1 = ? or @xxx2 = ?) begin …… end else begin …… end -- 循环语句 while (@xxx1 <> ?) begin …… end -- 游标： ---- 尽量避免使用游标 ---- 定义游标 declare cursor_xxx cursor for select col1, col2 … from tablename where col1 = @xxx; ---- 打开游标 open cursor_xxx; ---- 将游标中的值取到变量中 fetch next from cursor_xxx into @xxx1, @xxx2 …; ---- 开始游标循环 while (@@fetch_status = 0) begin …… fetch next from cursor_xxx into @xxx1, @xxx2 …; end ---- 结束游标 close cursor_xxx; ---- 销毁游标 deallocate cursor_xxx; -- 事务 ---- 如果显式使用事务，请注意SQL Server默认的事务隔离级别是读提交（Read Committed） ---- 如果使用更高级别的事务隔离级别，请详细阅读帮助文档，避免不必要的锁阻塞 begin tran update ……; commit; -- 异常处理机制 ---- 1、在第一次使用异常处理机制之前声明异常变量 ---- 2、对容易发生错误的操作，用trycatch进行异常捕获（声明变量不需要） ---- 3、在清理资源后，将错误记录保存在ExecProcdure_ErrorLog表中 ------ ExecProcdure_ErrorLog建表脚本如下： ------ create table dbo.ExecProcdure_ErrorLog( ------ [ID] [bigint] identity(1, 1) not null, ------ [ErrorNumber] [int] null, ------ [ErrorSeverity] [int] null, ------ [ErrorState] [int] null, ------ [ErrorProcedure] [nvarchar](200) null, ------ [ErrorLine] [nvarchar](50) null, ------ [ErrorMessage] [nvarchar](4000) null, ------ [ErrorDateTime] [datetime] null, ------ constraint [PK_ExecProcdure_ErrorLog] primary key clustered ------ ([ID] asc ) ------ with (IGNORE_DUP_KEY = OFF) ON [primary] ------ ) ON (primary] ---- 4、最后使用raiserror将错误返回给调用者 ---- 错误处理例子 …… begin try …… end try ---- begin catch 要紧跟着end try，中间不允许有其他语句 begin catch ---- 清理上面try中使用的资源，如删除临时表、销毁游标、回滚事务等 …… ---- 设置错误变量 set @ErrorMessage = ERROR_mESSAGE(), set @ErrorSeverity = ERROR_SEVERITY(), set @ErrorState = ERROR_STATE(); ---- 返回错误信息 raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState); ---- 保存错误信息 insert into ExecProcdure_ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime) select ERROR_NUMBER() as ErrorNumber, ERROR_SEVERITY() as ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage, getdate() as ErrorDateTime; end catch …… end