procedure和Function中动态SQL

 经常需要在procedure和Function中把参数作为查询条件作为动态的SQL，但procedure和Function有一些不同。

procedure:

DECLARE @nsql NVARCHAR(500)
DECLARE @i INT
SET @nsql = 'SELECT 1 FROM Rep_Fact_AvgSingleSample_Temp WHERE gid_SystemFactory = @gid_SystemFactory ';
	if(@gid_waveInfo IS NOT NULL)
		SET @nsql = @nsql + ' AND gid_waveInfo = @gid_waveInfo';
	if(@WaveName IS NOT NULL)
		SET @nsql = @nsql + ' AND WaveName = @WaveName';
	if(@gid_Areainfo IS NOT NULL)
		SET @nsql = @nsql + ' AND gid_Areainfo = @gid_Areainfo';
	if(@ObjectName IS NOT NULL)
		SET @nsql = @nsql + ' AND ObjectName = @ObjectName';

	SET @nsql  = @nsql + ' AND YearName = @YearName AND QuarterInfo = @QuarterInfo AND gid_Factory_QuestionList = @gid_Factory_QuestionList AND CycleType = @CycleType AND ObjectType = @ObjectType AND SelfLayer = @SelfLayer';
	exec sp_executesql @nsql;
	select @i = @@rowcount

 

但是在function中不能使用exec sp_executesql，所以需要使用另外一种方法

--1,若是采用动态语句
declare @sql varchar(1000)
set @sql='select * from tb where 1=1 '
if @p1 is not null
  set @sql=@sql + ' and field1=' + @p1
if @p2 is not null
  set @sql=@sql + ' and field2=' + @p2

--2,若是采用的非动态语句
select * from tb 
  where field1=case when @p1 is null then field1 else @p1 end
       and field2=case when @p2 is null then field2 else @p2 end

这样我就能使用下面的语句：

	IF EXISTS ( SELECT 1 FROM Rep_Fact_AvgSingleSample_Temp WHERE gid_SystemFactory = @gid_SystemFactory AND 
				gid_waveInfo = case when @gid_waveInfo is null then gid_waveInfo else @gid_waveInfo end AND
				WaveName = case when @WaveName is null then WaveName else @WaveName end AND
				gid_Areainfo = case when @gid_Areainfo is null then gid_Areainfo else @gid_Areainfo end AND
				ObjectName = case when @ObjectName is null then ObjectName else @ObjectName end AND
				YearName = @YearName AND QuarterInfo = @QuarterInfo AND gid_Factory_QuestionList = @gid_Factory_QuestionList AND 
				CycleType = @CycleType AND ObjectType = @ObjectType AND SelfLayer = @SelfLayer)   
	BEGIN 
		SELECT @ReturnValue = '1';
	END