WHERE IF - 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 2008 Forums

Transact-SQL (2008)

WHERE IF

Author

Topic

brmcdani44
Starting Member

4 Posts

Posted - 2012-03-01 : 12:31:01

Hello, I am trying to figure out how to make a WHERE IF CASE. Any help will be appreciated. Thanks

	SELECT * FROM vehicle
	WHERE
	CASE WHEN @make IS NOT NULL THEN
	VehMake LIKE @make
	CASE WHEN @model IS NOT NULL THEN
	AND VehModel LIKE @model
	CASE WHEN @year IS NOT NULL THEN
	AND Vehyr LIKE @year
	CASE WHEN @color IS NOT NULL THEN
	AND VehColor LIKE @color

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-01 : 13:30:27

That is called a Catch-All Query. Perhaps this link will help:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 13:51:26

this can cause bad plans to be generated and affect performance particularly for large datasets. So if performance is affected, try using dynamic sql approach

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources