Where condition - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Where condition

Author

Topic

olily
Starting Member

37 Posts

Posted - 2002-06-26 : 23:17:07

If have a simple stored procedures as below:
-----------------------
CREATE PROCEDURE TEST
@Param1 char(10),
@Param2 char(10),
@Param3 char(10)

AS

SELECT * FROM TABLE1 WHERE COL1 = @Param1
------------------------

I would like to include @Param2 and @Param3 as search condition whenever it contains string. If @Param2 = '', then I will just ignore @Param2. But if @Param3 <> '', then my SELECT statement will be as follow: SELECT * FROM TABLE1 WHERE COL1 = @Param1 AND COL3 = @Param3
This apply to @Param2 as well.

I was thinking of using a Case or If statement incorporate in on sql statement but this seems cannot work. I tried to reduce duplicate code in my stored proc. The easiest way I can think of is use IF and define a few similar sql according to @Param2 and @Param3.
Please advice.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-26 : 23:23:16

Hi

How about using a technique like this



CREATE PROCEDURE TEST 
@Param1 char(10), 
@Param2 char(10), 
@Param3 char(10) 

AS 

if @Param1 = ''
Select @Param1 = NULL

if @Param2 = ''
Select @Param2 = NULL

if @Param3 = ''
Select @Param3 = NULL

SELECT * FROM TABLE1 
WHERE
 Col1 = isNull(@Param1, Col1) and
 Col2 = isNull(@Param2, Col2) and
 Col3 = isNull(@Param3, Col3)

Note that in this case, isNull and coalesce are interchangeable.

Hope that helps

Damian

thirumal
Starting Member

1 Post

Posted - 2002-06-27 : 01:54:32

Hi,

The Best way to solve the problem is to go for optional parameter

CREATE PROCEDURE TEST
@Param1 char(10)=null,
@Param2 char(10)=null,
@Param3 char(10)=null

AS

SELECT * FROM TABLE1 WHERE COL1 = @Param1 and COL2 = @Param2 and
COL3 = @Param3

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-27 : 02:00:46

My way is a way of implementing optional parameters that is a little more ADO friendly.

As for the second part. Your query is wrong. Nothing equals NULL. That is why you need to use isNull or coalesce

Damian

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 05:12:35

create table TABLE1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)

declare @param1 varchar(10)
declare @param2 varchar(10)
declare @param3 varchar(10)

select * from TABLE1
where col1 = @param1
and col2 = (case when @param2 <> '' then @param2 else col2 end)
and col3 = (case when @param3 <> '' then @param3 else col3 end)

No need for any procedural IFs...

Whats the fuss all about????

<<monet makes money>>

olily
Starting Member

37 Posts

Posted - 2002-06-27 : 19:59:16

Thanks guys! After I have tested, it finally works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources