SQL學習筆記之存儲過程的編寫

今天寫幾個存儲過程，覺得有這個必要記錄下來，方便以后忘了也好有個備份，都很簡單，高手可以不用看的。

一、記錄的插入

--region?[dbo].[InsertArchive]

------------------------------------------------------------------------------------------------------------------------
--?Generated?By:???wangzeng?using?CodeSmith?4.0.0.0
--?Template:???????StoredProcedures.cst
--?Procedure?Name:?[dbo].[InsertArchive]
--?Date?Generated:?2007年11月28日
------------------------------------------------------------------------------------------------------------------------

--插入檔案記錄

ALTER?PROCEDURE?[dbo].[InsertArchive]
????@Name?varchar(50),
????@Sex?int,
????@PostID?int,
????@OrgID?int,
????@WorkTypeID?varchar(20),
????@ArchivesID?int?OUTPUT
AS

--SET?NOCOUNT?ON

INSERT?INTO?[dbo].[Archives]?(
????[Name],
????[Sex],
????[PostID],
????[OrgID],
????[WorkTypeID]
)?VALUES?(
????@Name,
????@Sex,
????@PostID,
????@OrgID,
????@WorkTypeID
)

SET?@ArchivesID?=?SCOPE_IDENTITY()

--endregion

二、動態查詢，這個是由codesmit生成的，然后稍微改了點，條件可以任意組合

--動態查詢定單
ALTER?PROCEDURE?[dbo].[SelectOrdersDynamic]
????@WhereCondition?nvarchar(500),
????@OrderByExpression?nvarchar(250)?=?NULL
AS

SET?NOCOUNT?ON
SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED

DECLARE?@SQL?nvarchar(3250)

SET?@SQL?=?'
SELECT
????[ID],
????[OrderID],
????Orders.SupplierCode,
????SupplierName,
????''AddDate''=case?when?Orders.AddDate<>''1900-1-1''?then
????????????????????????????convert(nvarchar,year(Orders.AddDate))+''-''+convert(nvarchar,month(Orders.AddDate))+''-''+convert(nvarchar,day(Orders.AddDate))
??????????????????????else??''''
??????????????????????end,
?????''ClaimDate''=case?when?ClaimDate<>''1900-1-1''?then
????????????????????????????convert(nvarchar,year(ClaimDate))+''-''+convert(nvarchar,month(ClaimDate))+''-''+convert(nvarchar,day(ClaimDate))
????????????????????????else?''''
????????????????????????end,
????''FactDate''=case??when?FactDate<>''1900-1-1''?then?convert(nvarchar,year(FactDate))+''-''+convert(nvarchar,month(FactDate))+''-''+convert(nvarchar,day(FactDate))
???????????????????????else??''''?????????????????????
??????????????????????end,
????[Lister],
????[Assessor],
????[Validate],
????[Auditing],
????[Perform],
????[IsClose],
????''ValidateState''=case?
????????????????????????when?Validate=1?then?''是''
????????????????????????when?Validate=0?then?''否''
????????????????????end,
????''AuditingState''=case?
????????????????????when?Auditing=1?then?''是''
????????????????????when?Auditing=0?then?''否''
????????????????????end,
????''PerformState''=case?
????????????????????when?Perform=0?then?''未執行''
????????????????????when?Perform=1?then?''執行中''
????????????????????when?Perform=2?then?''執行完成''
????????????????end,
????''IsCloseState''=case
????????????????when?IsClose=1?then?''關閉''
????????????????when?IsClose=0?then?''正常''
????????????????end,
????''ValidateUrl''=case?
????????????????????????when?Validate=0?then?''OrderDetail.aspx?OrderID=''+OrderID
????????????????????????else?''''
????????????????????end,
????''ValidateCss''=case?
????????????????????????when?Validate=0?then?''bluelink''
????????????????????????else?''nolink''
????????????????????end,
????''ShipmentUrl''=case?
????????????????????????when?Perform=1?then?''Shipment.aspx?OrderID=''+OrderID
????????????????????????else?''''
????????????????????end,
????''ShipmentCss''=case?
????????????????????????when?Perform=1?then?''bluelink''
????????????????????????else?''nolink''
????????????????????end,
????SupplierName,
??0?as?''TotalMaterialNum'',
??0?as?????''LackMaterialNum'',
??0.0?as?''TotalMoney'',
??PayMode,
??Remark,
??Orders.OtherValue
FROM
????Orders?left?join?Supplier?on?Orders.SupplierCode=Supplier.SupplierCode
WHERE
????'?+?@WhereCondition

IF?@OrderByExpression?IS?NOT?NULL?AND?LEN(@OrderByExpression)?>?0
BEGIN
????SET?@SQL?=?@SQL?+?'
ORDER?BY
????'?+?'ID?desc'--@OrderByExpression
END

EXEC?sp_executesql?@SQL

三、修改記錄，這個存儲過程這么寫主要是為了修改的時候方便，因為如果你不要修改的地方就不要傳參數，傳統的存儲過程是把原來的參數重傳一次，這個靈活點。

--修改定單信息
ALTER?PROCEDURE?[dbo].[UpdateOrder]
????@OrderID?varchar(200),
????@SupplierCode?varchar(200),
????@AddDate?datetime,
????@ClaimDate?datetime,
????@FactDate?datetime,
????@Lister?varchar(50),
????@Assessor?varchar(50),
????@Validate?int,
????@Auditing?int,
????@Perform?int,
????@IsClose?int,
????@PayMode?varchar(50),
????@Remark?nvarchar(500),
????@OtherValue?varchar(50)
AS

--SET?NOCOUNT?ON


declare?@SQL?nvarchar(1000)

set?@SQL='UPDATE?Orders?'

if(len(@SupplierCode)>0)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',SupplierCode='+''''+convert(varchar(200),@SupplierCode)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?SupplierCode='+''''+convert(varchar(200),@SupplierCode)+''''
????end
end
if(@AddDate<>'1900-1-1')
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',AddDate='+''''+convert(varchar,@AddDate)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?AddDate='+''''+convert(varchar,@AddDate)+''''
????end
end
if(@ClaimDate<>'1900-1-1')
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',ClaimDate='+''''+convert(varchar,@ClaimDate)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?ClaimDate='+''''+convert(varchar,@ClaimDate)+''''
????end
end
if(@FactDate<>'1900-1-1')
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',FactDate='+''''+convert(varchar,@FactDate)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?FactDate='+''''+convert(varchar,@FactDate)+''''
????end
end
if(len(@Lister)>0)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',Lister='+''''+convert(varchar,@Lister)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?Lister='+''''+convert(varchar,@Lister)+''''
????end
end
if(len(@Assessor)>0)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????if?@Assessor='null'
????????begin
????????????set?@Assessor=''
????????end
????????set?@SQL=@SQL+',Assessor='+''''+convert(varchar,@Assessor)+''''
????end
????else
????begin
????if?@Assessor='null'
????????begin
????????????set?@Assessor=''
????????end
????????set?@SQL=@SQL+'?set?Assessor='+''''+convert(varchar,@Assessor)+''''
????end
end
if(cast(@Validate?as?int)>-1)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',Validate='+''''+convert(varchar,@Validate)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?Validate='+''''+convert(varchar,@Validate)+''''
????end
end
if(cast(@Auditing?as?int)>-1)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',Auditing='+''''+convert(varchar,@Auditing)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?Auditing='+''''+convert(varchar,@Auditing)+''''
????end
end
if(cast(@Perform?as?int)>-1)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',Perform='+''''+convert(varchar,@Perform)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?Perform='+''''+convert(varchar,@Perform)+''''
????end
end
if(cast(@IsClose?as?int)>-1)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',IsClose='+''''+convert(varchar,@IsClose)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?IsClose='+''''+convert(varchar,@IsClose)+''''
????end
end
if(len(@PayMode)>0)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',PayMode='+''''+convert(varchar,@PayMode)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?PayMode='+''''+convert(varchar,@PayMode)+''''
????end
end
if(len(convert(nvarchar,@Remark))>0)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',Remark='+''''+convert(nvarchar(500),@Remark)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?Remark='+''''+convert(nvarchar(500),@Remark)+''''
????end
????--update?Orders?set?Remark=@Remark?where?OrderID=+''''+@OrderID+''''
end
if(len(@OtherValue)>0)
begin
????if(len(@SQL)>len('UPDATE?Orders?set?'))
????begin
????????set?@SQL=@SQL+',OtherValue='+''''+convert(varchar,@OtherValue)+''''
????end
????else
????begin
????????set?@SQL=@SQL+'?set?OtherValue='+''''+convert(varchar,@OtherValue)+''''
????end
end
set?@SQL=@SQL+'?where?OrderID='+''''+convert(nvarchar(200),@OrderID)+''''
print?@SQL
exec(@SQL)

if(len(@SupplierCode)>0)
begin
????update?Supplier?set?LastUseDate=getdate()?where?SupplierCode=@SupplierCode????--沒有統計修改的次數，因為修改時經常是同一個供應商
end

/**//*
UPDATE?[dbo].[Orders]?SET
????[SupplierCode]?=?@SupplierCode,
????[AddDate]?=?@AddDate,
????[ClaimDate]=@ClaimDate,
????[FactDate]?=?@FactDate,
????[Lister]?=?@Lister,
????[Assessor]?=?@Assessor,
????[Validate]?=?@Validate,
????[Auditing]?=?@Auditing,
????[Perform]?=?@Perform
WHERE
????[OrderID]?=?@OrderID

*/