總結的一個存儲過程開發模板，主要是避免一些常見問題。 書寫規范為：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
|