建立搜寻程序 [英] creating search procedure
问题描述
大家好,
我想创建一个用于搜索目的的过程.
它具有4个参数,例如姓名,地址,薪水和职位.
用户还可以基于单个参数或两个或三个参数进行搜索.
这意味着用户应该能够接受空参数.
我应该怎么做.
在此先感谢
Hi guys,
I want to create a procedure for searching purposes.
It has 4 parameters, like name, address, salary and post.
The user can also search on the base of a single parameter or two or three parameters.
This means user should be able to accept null parameters.
How should I do this.
Thanks in advance
推荐答案
使用TSQL的IF语句检查参数是否为NULL: ^ ]
Use TSQL''s IF statement to check if a parameter is NULL: http://msdn.microsoft.com/en-us/library/ms187471.aspx[^]
IF @param1 IS NULL
BEGIN
...
END
...
然后相应地构造您的SQL.
或者,您可以选择对存储过程的参数定义使用默认值: ^ ].
祝您编程愉快!
and construct your SQL accordingly.
As an alternative you may choose to use default values for the parameter definitions of your stored procedure: http://forums.aspfree.com/development-articles-42/using-default-parameter-values-for-t-sql-stored-procedures-40921.html[^].
Happy coding!
创建像这样的动态查询
并将一个参数传递给数据库
create dynamic query like that
and pass one parameter to database
if (InterviewCategoryId == 0)
{
if (DateTo == "" && SearchByName == "" && DateFrom != "")
{
Query += " ";
}
else if (DateTo != "" && DateFrom == "" && SearchByName == "")
{
Query += " ";
}
else if (DateTo == "" && DateFrom == "" && SearchByName != "")
{
Query += " ";
}
else if (DateTo != "" && DateFrom != "" && SearchByName != "")
{
Query += "";
}
else
{
Query += "";
}
pass it to the DataBase as one Parameter;
Obj.fn_GetSearcgCandidatesResult(Query);
在存储过程中声明您这样的变量,如果您的整数为null,则将其发送为null,不要将其发送为零,否则您必须在sqlserver中添加另一个条件.
in store procedure declare youe variable like this and if your integer is null send it as null don''t send it as zero otherwise you have to add another condition in sqlserver.
@parametre1 int=null,
@parametre2 varchar(50)=null,
@parametre3 varchar(50)==null,
@parametre4 varchar(50)==null
然后检查参数是否为null并编写您自己的查询
then check if parametre is null or not and write your respective query
这篇关于建立搜寻程序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!