總結(jié)的一個(gè)存儲(chǔ)過(guò)程開發(fā)模板，主要是避免一些常見問(wèn)題。 書寫規(guī)范為：SQL關(guān)鍵字均使用小寫。但是定義連接屬性，如SET NOCOUNT ON可以用大寫；變量采用Camel風(fēng)格，單詞首字符大寫。
  /**//**********************************************
 作者： XXX
創(chuàng)建日期：YYYY-MM-DD
功能描述：（清楚、詳盡。如：本存儲(chǔ)過(guò)程主要用于生成主鍵ID，
為了適應(yīng)分布式數(shù)據(jù)庫(kù)的應(yīng)用，采用SiteID（三位）+YYYYMMDD（八位）+順序位（八位）組成BigInt類型編碼。）
-----------------------------------------------
修改者：
修改日期：
修改描述：（如：本次修改加入了對(duì)存儲(chǔ)過(guò)程的錯(cuò)誤捕獲。）
-----------------------------------------------
傳入?yún)?shù)：
@X1
數(shù)據(jù)類型：
描述：
@X2
數(shù)據(jù)類型：
描述：
傳出參數(shù)：
@X1
數(shù)據(jù)類型：
描述：
返回值：
@XX
數(shù)據(jù)類型：
描述：
 **********************************************/
 -- 先檢查存儲(chǔ)過(guò)程是否存在，如果存在，先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
-- 出于性能考慮，這是每個(gè)存儲(chǔ)過(guò)程的第一條語(yǔ)句
-- 當(dāng)SET NOCOUNT為ON時(shí)，將不向客戶端發(fā)送存儲(chǔ)過(guò)程中每個(gè)語(yǔ)句的DONE_IN_PROC消息。
-- 如果存儲(chǔ)過(guò)程中包含的一些語(yǔ)句并不返回許多實(shí)際數(shù)據(jù)，則該設(shè)置由于大量減少了網(wǎng)絡(luò)流量，因此可顯著提高性能。
SET NOCOUNT ON
-- 定義錯(cuò)誤變量，為raiserror使用
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
declare @ErrorState int;
-- 注釋：
---- 1、為方便調(diào)試，在存儲(chǔ)過(guò)程內(nèi)部一律使用“--”代替“/* */”
---- 2、每個(gè)關(guān)鍵性操作請(qǐng)?jiān)谇懊孀⑨?/span>
-- 變量定義：
---- 1、不要在循環(huán)中定義變量
---- 2、如果變量是用于存儲(chǔ)某個(gè)字段的值，請(qǐng)使變量類型（包括精度）和字段類型一致
---- 3、最好顯示的為變量初始化
-- 變量賦值：
---- 使用set @xxx = ?，不要用早期版本的select @xxx = ?方式
---- 從SQL中為變量賦值采用
select @xxx1 = col1,
@xxx2 = col2
from tabelname
-- 一次性清空表，請(qǐng)使用truncate代替delete
truncate table tablename
-- insert語(yǔ)句要把字段名寫全
insert into tablename(col1, col2…)
values(@xxx1, @xxx2…);
-- 批量插入
insert into tablename1(col1, col2…)
select col1, col2…
from tablename2
……
-- 判斷語(yǔ)句
if (@xxx1 = ? or @xxx2 = ?)
begin
……
end
else
begin
……
end
-- 循環(huán)語(yǔ)句
while (@xxx1 <> ?)
begin
……
end
-- 游標(biāo)：
---- 盡量避免使用游標(biāo)
---- 定義游標(biāo)
declare cursor_xxx cursor for
select col1, col2 …
from tablename
where col1 = @xxx;
---- 打開游標(biāo)
open cursor_xxx;
---- 將游標(biāo)中的值取到變量中
fetch next from cursor_xxx
into @xxx1, @xxx2 …;
---- 開始游標(biāo)循環(huán)
while (@@fetch_status = 0)
begin
……
fetch next from cursor_xxx
into @xxx1, @xxx2 …;
end
---- 結(jié)束游標(biāo)
close cursor_xxx;
---- 銷毀游標(biāo)
deallocate cursor_xxx;
-- 事務(wù)
---- 如果顯式使用事務(wù)，請(qǐng)注意SQL Server默認(rèn)的事務(wù)隔離級(jí)別是讀提交（Read Committed）
---- 如果使用更高級(jí)別的事務(wù)隔離級(jí)別，請(qǐng)?jiān)敿?xì)閱讀幫助文檔，避免不必要的鎖阻塞
begin tran
update ……;
commit;
-- 異常處理機(jī)制
---- 1、在第一次使用異常處理機(jī)制之前聲明異常變量
---- 2、對(duì)容易發(fā)生錯(cuò)誤的操作，用try catch進(jìn)行異常捕獲（聲明變量不需要）
---- 3、在清理資源后，將錯(cuò)誤記錄保存在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將錯(cuò)誤返回給調(diào)用者
---- 錯(cuò)誤處理例子
……
begin try
……
end try
---- begin catch 要緊跟著end try，中間不允許有其他語(yǔ)句
begin catch
---- 清理上面try中使用的資源，如刪除臨時(shí)表、銷毀游標(biāo)、回滾事務(wù)等
……
---- 設(shè)置錯(cuò)誤變量
set @ErrorMessage = ERROR_mESSAGE(),
set @ErrorSeverity = ERROR_SEVERITY(),
set @ErrorState = ERROR_STATE();
---- 返回錯(cuò)誤信息
raiserror (@ErrorMessage,
@ErrorSeverity,
@ErrorState);
---- 保存錯(cuò)誤信息
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
|