经常需要在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