SqlServer多条件组合查询的三种实现方式

  开发中经常会遇得到需要多种条件组合查询的情况，比如有三个表，年级表Grade(GradeId,GradeName)，班级Class(ClassId,ClassName,GradeId)，学员表Student(StuId,StuName,ClassId)，现要求可以按年级Id、班级Id、学生名，这三个条件可以任意组合查询学员信息。

create proc combocondition
    @gradeId int,
    @classId int,
    @stuName nvarchar(10)
as
    select s.*,c.classname,g.gradename from student s join class c on s.classid=c.classid join grade g on c.gradeid=g.gradeid
where
    (@gradeId is null or g.gradeid=@gradeId) and
    (@classId is null or c.classid=@classId) and
    (@stuName is null or s.stuName=@stuName)
go

  那么：

  exec combocondition null,null,null --这是查询所有学员信息；
　　　
  exec combocondition 2,null,null --这是查询年级Id为2的学员信息；
　
  exec combocondition null,4,null --这是查询班级Id为4的学员信息；

  exec combocondition 2,4,null --这是查询年级Id为2且班级Id为4的学员信息；

  也可以使用case when：

create proc combocondition
    @gradeId int,
    @classId int,
    @stuName nvarchar(10)
as
    select s.*,c.classname,g.gradename from student s join class c on s.classid=c.classid join grade g on c.gradeid=g.gradeid
where
    (case when @gradeId is not null then g.gradeid=@gradeId else 1=1) and
    (case when @classId is not null then g.gradeid=@classId else 1=1) and
    (case when @stuName is not null then g.gradeid=@stuName else 1=1)
go

  也可以拼接SQL字符串：

create proc combocondition
    @gradeId int,
    @classId int,
    @stuName nvarchar(10),
    @SQL 	 nvarchar(4000)
as
begin
set @SQL = '
    select 
    	s.*,c.classname,g.gradename 
    from 
    	student s 
    join 
    	class c on s.classid=c.classid 
    join 
    	grade g on c.gradeid=g.gradeid
  	where
  		1=1
'
if @gradeId is not null and @gradeId <> ''
begin
	set @SQL = @SQL + 'and gradeId =' + ''''+@gradeId+''''
end
if @classId is not null and @classId <> ''
begin
	set @SQL = @SQL + 'and classId =' + ''''+@classId+''''
end
if @stuName is not null and @stuName <> ''
begin
	set @SQL = @SQL + 'and stuName =' + ''''+@stuName+''''
end

print(@SQL)

execute sp_executesql @SQL