optional parameter search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

optional parameter search

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2010-04-22 : 19:14:38

I am doing a search with optional parameters.
This involve a left outer join.

create procedure sp_Search
(
@Name varchar(50)=null,
@Number varchar(50)=null,
@verified bit=null,
@projectNumber int null

)
as
begin

Select v.ID,
v.Title,
v.Gross,
....
from

View1 v left outer join Table1 i on
v.number = i.num
where
(v.name=COALESCE(@Name,v.name)) and
(v.number=COALESCE(@Number,v.number))and
(v.verified=COALESCE(@verified,v.verified)) and

(i.project_number=COALESCE(@projectNumber,i.project_number))

end

I have four parameters so if i give no parameter I need the entire result with out any filtering.
If I give one or more parameter then it should filter based on the parameter. from the entire result set.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-22 : 19:23:17

What was the question?

I've always used this style:

WHERE (v.name = @Name) OR @Name IS NULL and...

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:21:59

quote:
Originally posted by DBA in the making

What was the question?

I've always used this style:

WHERE (v.name = @Name) OR @Name IS NULL) and...

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-23 : 03:36:49

quote:
Originally posted by visakh16

quote:
Originally posted by DBA in the making

What was the question?

I've always used this style:

WHERE (v.name = @Name) OR @Name IS NULL) and...

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Is there an echo in here?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 04:03:01

you have put braces at wrong position. putting OR @Name IS NULL outside bypasses all the other checks. you need to wrap it with first condition itself

WHERE (v.name = @Name OR @Name IS NULL)
AND...

not

WHERE (v.name = @Name) OR @Name IS NULL AND...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-23 : 04:20:52

Of course. Yes, that's what I meant. THanx

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 04:50:14

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16

quote:
Originally posted by DBA in the making

What was the question?

I've always used this style:

WHERE (v.name = @Name) OR @Name IS NULL) and...

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Is there an echo in here?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

He highlighed it with red color with strike so it is not an echo

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:06:16

quote:
Originally posted by DBA in the making

Of course. Yes, that's what I meant. THanx

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

No problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources