Development often encounter the need for a variety of conditional combination of inquiries, such as three tables, Grade table grade (Gradeid,gradename), Class Class (Classid,classname,gradeid), Student table student (Stuid, STUNAME,CLASSID), now requires that can be based on grade ID, class ID, student name, these three conditions can be any combination of query student information.
There is more than one, and here's a clever way to use a query, and we'll write a process that reads:
Copy Code code as follows:
Create proc Up_select_student_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 =-1 or g.gradeid= @gradeId) and
(@classId =-1 or c.classid= @classId) and
(@stuName = ' or s.stuname= @stuName)
Go
--Then:
exec up_select_student_combocondition-1,-1, '--it's all about the student information.
exec up_select_student_combocondition 2,-1, '--this is the student information for the Grade ID 2.
exec up_select_student_combocondition-1,4, '--this is the student information that queries class ID 4
EXEC up_select_student_combocondition 2, 4, '--this is a student message with a grade ID of 2 and a class ID of 4
--etc .....
