Conditional where clause and inner join

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 2005 Forums

Transact-SQL (2005)

Conditional where clause and inner join

Author

Topic

san3297
Starting Member

7 Posts

Posted - 2010-10-27 : 14:28:29

I have a stored proc for which i am passing values. For instance

Declare @Type varchar(10)
Declare @Plan varchar(10)
Declare @Guage varchar(10)
Declare @Size varchar(10)

Now i can have 3 different values for type. If type is 1 and plan value is not null my sql should be like

Select * from vendors v inner join customers c on v.id=c.id
where v.plan=@plan

If type is 2 and plan value is null my sql should be like

Select * from vendors

If type is 2 and plan values are not null my sql should be like

Select * from vendors where v.plan=@plan and v.guage=@Guage and
v.size= @size.

How can i accomodate all these conditions in one sql query. When all the values are there where should execute if not it should return all the rows. I can use a dynamic sql but want to try this way and does not want to use different sql queries for each scenario as my real query is fairly large. ANy inputs please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 14:35:19

[code]
Select * from vendors v
left join customers c on v.id=c.id
where (v.plan=@plan or @plan is null)
and (c.id is not null or @type=2)
and (v.guage=@Guage or @guage is null or @type <> 2)
and (v.size= @size or @size is null or @type <> 2)
[/code]

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-27 : 16:28:26

It would be better to use dynamic SQL with sp_executesql with the paramaters passed to sp_executesql and build the WHERE clause based on the parameters that are actually used.

A static query will multiple nullable parameters will be very likely to perform badly.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources