creating search procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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

Posted 7-Apr-11 21:14pm

pawanvats

Updated 7-Apr-11 22:02pm

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 8-Apr-11 4:03am

Edited for Readabiliy.

3 solutions

Solution 2

create dynamic query like that

and pass one parameter to database

C#

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);

Posted 7-Apr-11 21:36pm

Mahendra.p25

Solution 3

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

then check if parametre is null or not and write your respective query

Posted 7-Apr-11 23:42pm

ParthaDinda

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2011-04-07T21:19:00

Solution 1

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

...

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!

Posted 7-Apr-11 21:19pm

Manfred Rudolf Bihy

Updated 7-Apr-11 21:21pm

v3

Comments

Sandeep Mewara 8-Apr-11 3:30am

My 5!

Dalek Dave 8-Apr-11 4:03am

Good Answer.

Wendelius 8-Apr-11 13:11pm

Nice :)