总结的一个存储过程开发模板，主要是避免一些常见问题。 书写规范为：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、对容易发生错误的操作，用try catch进行异常捕获（声明变量不需要）
---- 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
|