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

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-01-08 : 21:05:21

Hi All,

How can i do if OppNum <>'' then run the statement of A.opportunitynumber = @OppNum And else ignore this criteria.

select * from .....
where
if(@OppNum<>'') then
A.opportunitynumber = @OppNum And
end if
O.statuscode = '200012'

Please advise.

Thank you.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-01-08 : 21:41:32

Hi All,

I tried using like case .. when taking too long time. More than 2 minute to run.

Please advise if anyone have faster method to solved my issue

select * from .....
where
O.statuscode = '200012'
AND a.opportunitynumber LIKE
CASE WHEN (@OppNum<>'') THEN
@OppNum
ELSE
'%'+ @OppNum +'%'
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-08 : 22:05:55

[code]SELECT *
FROM .....
WHERE O.statuscode = '200012'
AND (a.opportunitynumber = @OppNum OR @OppNum = '')
[/code]Once the query is working and IF you are running into performance problems, check out this page: http://www.sommarskog.se/dyn-search-2008.html

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-09 : 05:51:37

this is one of the few times when (In my opinion) dynamic SQL is the best way to go:

If you has multiple conditions like that (which is a pretty common webform type requirement), then a construct like this:



DECLARE @optionalParam1 INT = 4
      , @optionalParam2 INT = NULL
      , @optionalParam3 CHAR(4) = NULL
      
DECLARE @sql NVARCHAR(MAX) = N'
SELECT
	mt.[foo] AS [bar]
	, mt.[woo] AS [shoo]
FROM
	dbo.myTable AS mt
WHERE
	1 = 1'

IF @optionalParam1 IS NOT NULL SET @sql += N'
	AND mt.[Column1] = @optionalParam1'

IF @optionalParam2 IS NOT NULL SET @sql += N'
	AND mt.[Column2] = @optionalParam2'

IF @optionalParam3 IS NOT NULL SET @sql += N'
	AND mt.[Column3] = @optionalParam3'

PRINT @sql

-- Do it
EXEC sp_executesql
	@sql
	, N'@optionalParam1 INT
	, @optionalParam2 INT
	, @optionalParam3 CHAR(4)'
	, @optionalParam1
	, @optionalParam2
	, @optionalParam3

Will generate a reusable plan for each permutation of the search.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources