kinanson的技術回憶

最近專案又開始需要用sp了，有一陣子沒寫的我有點陌生，遇到比較特別的就再做點筆記。

 

Create PROCEDURE [dbo].[SP_ReadSearch]
	@TradeDate date,
	@CCYPair varchar(7),
	@PricerStatus varchar(1),
	@ProcessStatus varchar(1)
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE @WhereCombination varchar(200)
	DECLARE @Sql VARCHAR(MAX)
	DECLARE @OuptSql NVARCHAR(MAX)
--定義一個暫存table	
	DECLARE @T1 TABLE
	(
		CCYPair varchar(7),DCIDealID int,TradeDate date
		,ProcessStatus uniqueidentifier,PricerStatus varchar(1)
		,StatusReason VARCHAR(50),LastUpdateTime datetimeoffset
	)
	declare @ccount int
	set @WhereCombination=''
--把查詢組成字串
	set @Sql='select DD.CCYPair,DD.DCIDealID,DD.TradeDate,ISNULL(SM.SessionMainID,' + 
	'''00000000-0000-0000-0000-000000000000'') as ProcessStatus,LastDD.PricerStatus,' +
	'LastDD.StatusReason,LastDD.StatusDatetime as LastUpdateTime' +
	' from DCIDeal DD ' +
	'cross apply FN_GetLastDCIDealStatus(DD.DCIDealID) LastDD' +
	' left join SessionMain SM on DD.DCIDealID=SM.DCIDealID ' +
	'Where DD.TradeDate=''' + CAST(@TradeDate as VARCHAR(10)) + ''''
--下面則是動態組成where
	IF @CCYPair is NULL
	BEGIN
		set @WhereCombination+=' and 1=1'
	end
	ELSE
	BEGIN
		set @WhereCombination+=' and DD.CCYPair='''+ @CCYPair + ''''
	END
	IF @PricerStatus is NULL
	BEGIN
		set @WhereCombination+=' and 1=1'
	end
	ELSE IF(@PricerStatus='Y')
	BEGIN
		set @WhereCombination+=' and LastDD.PricerStatus=''Y'''
	END
	ELSE
	BEGIN
		set @WhereCombination+=' and LastDD.PricerStatus<>''Y'''
	END
	IF @ProcessStatus is NULL
	BEGIN
		set @WhereCombination+=' and 1=1'
	end
	ELSE IF(@ProcessStatus='A')
	BEGIN
		set @WhereCombination+=' and SM.SessionMainID is NULL'
	END
	ELSE
	BEGIN
		set @WhereCombination+=' and SM.SessionMainID is not NULL'
	END	
	set @WhereCombination+=' order by LastUpdateTime desc'
	set @OuptSql= @Sql + @WhereCombination
	insert into @T1 EXECUTE sys.sp_executesql @OuptSql
	select * from @T1
END

 

為何要把sql組成字串，因為sql和字串是無法用相加的方式，因為動態查詢是需要寫成字串去組sql，所以必須把兩段sql變成字串相加起來。

特別需要注意的是因為where string='string'需要使用兩個單引號，這邊需要多加兩個單引號才能組成一個單引號

最後要把字串變成正式sql語法，需要使用execute sp_executesql轉換，然後sql的字串型態必須用nvarchar才行。