mssql sql server 查询语句 where 多条件的查询数据的方法分享


摘要:
下文将讲述 where 多条件的添加方法
where子句功能:有条件的从表中获取满足条件的数据集合,
常同select update delete 关键字一起使用。



方法1:拼接sql语句生成where查询脚本

  declare @sql nvarchar(4000)
  set @sql =N'select [列A],[列B],[列C] from [tableName] where 1=1';

  if isnull(@tiaoJian1,'') !=''
  begin
    set @sql =@sql +' and [列A] like ''%'+@tiaoJian1+'%'' '
  end
  
  if isnull(@tiaoJian2,'') !=''
  begin
    set @sql =@sql +' and [列B] like ''%'+@tiaoJian2+'%'' '
  end

  exec(@sql)
  ---拼接sql方式执行sql语句


方法2:参数化拼接sql where 查询脚本

   declare @sql nvarchar(4000)
  set @sql =N'select [列A],[列B],[列C] from [tableName] where 1=1';

  if isnull(@tiaoJian1,'') !=''
  begin
    set @sql =@sql +' and [列A] like ''%+@tiaoJian1+%'' '
  end
  
  if isnull(@tiaoJian2,'') !=''
  begin
    set @sql =@sql +' and [列B] like ''%+@tiaoJian2+%'' '
  end

  exec sp_executesql @sql,
  N'@tiaoJian1 nvarchar(30),@tiaoJian2 nvarchar(30) ',@tiaoJian1 ='测试',@tiaoJian2='测试2'  
  ----参数化执行where 语句