搜索条件存储过程 [英] Stored Procedure for search criteria

问题描述

我有这些桌子
学校-(SchoolID，SchoolName，城市，电话，说明)
程序-(ProgramID，ProgramName)
课程-(CouseID，CourseName)
SCHOOL_PROGRAM_COUSRE-(课程ID，学校ID，程序ID，课程费用，学校课程名称)
类别-(CategoryID，CategoryName)

现在，用户在搜索框中输入的数据是-
1.CategoryName
2.学校名称
3.CourseName
4.课程费用

他可以输入一个，两个或三个搜索选项

现在，要显示给用户的结果来自两个表，即SCHOOL和SCHOOL_PROGRAM_COURSE，应显示给用户的列是-

城市，电话，说明，学校课程名称


如何为此做存储程序?我从上一个星期开始执行这些操作，但每次都失败.请尽快提供帮助.

谢谢

Hi,
I have these tables
SCHOOL- (SchoolID,SchoolName,City,Phone, Description)
PROGRAM- (ProgramID, ProgramName)
COURSE-(CouseID,CourseName)
SCHOOL_PROGRAM_COUSRE - (CourseID,SchoolID,ProgramID,CourseFee,SchoolCourseName)
CATEGORY - (CategoryID,CategoryName)

Now the data which user is entering in the search box are-
1.CategoryName
2.SchoolName
3.CourseName
4.CourseFee

He can enter one,two or maybe three options for search

Now the result which is coming to user are coming from two tables i.e SCHOOL and SCHOOL_PROGRAM_COURSE and the columns which should be displayed to user are-

City, Phone, Description, SchoolCourseName


How to make store procedure for this? I am doing these from last 1 week but failed every time.Please help ASAP.

Thanks

推荐答案

要使用存储过程并避免使用动态SQL，我建议您的参数具有默认的NULL值

例如:

To do this using a stored procedure and avoiding dynamic SQL, I recommend your parameters have default NULL values

For example:

@SchoolName varchar(50) = NULL

如果用户未选择学校名称，请不要将其传递.

在SELECT语句中，进行联接:

If the user does not choose a school name, then don''t pass it in.

In the SELECT statement, do your joins:

SELECT S.*, SPC.*
FROM SCHOOL S
INNER JOIN SCHOOL_PROGRAM_COURSE SPC ON S.SchoolID = SPC.SchoolID
INNER JOIN PROGRAM P ON P.ProgramID = P.ProgramID
...

魔术发生在WHERE子句中:

And the magic happens in the WHERE clause:

WHERE (@SchoolName IS NULL OR S.SchoolName = @SchoolName)
  AND (@(other parameter) IS NULL OR ...)

希望对您有所帮助.

Hope that helps.

在任何列上使用group by子句.

Use group by clause on any of the column.

这篇关于搜索条件存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！