how to write CASE in where Clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to write CASE in where Clause?

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-03-08 : 06:31:24

Hi All

How can i use CASE in WHERE clause (need to avoid dynamic sql)

i need to add the below condition in where

if (@date1 is not null) then (startdate >=@date1 and startdate< @date2)

if (@date2 is not null) then (startdate >=@date2 and startdate< @date3)

how can i write these conditions in where clause without using dynamic sql.

Thx in advance.

Thanks

Jack

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 07:36:00

WHERE
(
(@date1 is not null and startdate >=@date1 and startdate< @date2)
or
(@date2 is not null and startdate >=@date2 and startdate< @date3)
)

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-08 : 08:04:33

that wouldn't work -- I think

If both dates are null then your WHERE clause madhivanan wouldn't return any results. However in dynamic sql implementation it would result in no conditions so would return all results.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 08:37:01

OK

WHERE
(
(@date1 is not null and startdate >=@date1 and startdate< @date2)
or
(@date2 is not null and startdate >=@date2 and startdate< @date3)
or
(@date1 is null and @date2 is null)
)

Madhivanan

Failing to plan is Planning to fail

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-03-08 : 09:44:59

Thanks :)

Will it improve performance compared to Dynamic sql?

Thanks

Jack

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-08 : 10:17:48

Probably not. If you think about it the dynamic sql wouldn't have to consider a bunch of the conditions in the worst case

Saying that though -- because you are referencing the same column (startDate) each time then you won't see inappropriate index lookups due to cached plans so.... who knows.

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

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-03-08 : 10:37:41

Can you please suggest me the best way to achieve this?

actually the SP which is containing the above dynamic sql is taking 250 mmm. In this sp, only the EXEC sp_executesql part itslef is taking 150 mmm. This SP is getting called more than 20 times.

So i am trying to reduce the exec sp_executesql time part. That's why i am looking for alternate ways.

Kindly help me.

Thanks in advance

Thanks

Jack

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-08 : 11:28:00

Have you tired looking at the execution plan from the query in a stand along mode (another query analyser window).

I'm guessing that the actual query is more complex than you have written.

Do you have good indices? If you post the full dynamic sql block we'd be able to help more.

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

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-03-09 : 10:03:09

Here is the dynamic query

DECLARE @TABLE_A TABLE
(
ID INT IDENTITY(1,1),
XMLCOLUMN XML,
STARTDATE DATETIME,
ENDDATE DATETIME
)
GO

DECLARE @DATEFROM DATETIME,
@DATETO DATETIME,
@DTFROM1 DATETIME,
@DTFROM2 DATETIME,
@DATETO1 DATETIME,
@DATETO2 DATETIME,
@Query VARCHAR(MAX)

SET @Query = ''
SET @Query = @Query + 'SELECT XMLCOLUMN ' + CHAR(10)
SET @Query = @Query + 'FROM TABLE_A ' + CHAR(10)
SET @Query = @Query + 'WHERE (STARTDATE >= @DATEFROM AND STARTDATE < @DATETO) ' + CHAR(10)
SET @Query = @Query + ' OR (STARTDATE < @DATEFROM AND DOCUMENTENDDATE >= @DATEFROM)) ' + CHAR(10)

IF(@DTFROM1 IS NOT NULL AND @DATETO1 IS NOT NULL)
SET @Query = @Query + ' AND NOT (STARTDATE >= @DTFROM1 AND STARTDATE < @DATETO1)' + CHAR(10)

IF(@DTFROM2 IS NOT NULL AND @DATETO2 IS NOT NULL)
SET @Query = @Query + ' AND NOT (STARTDATE >= @DTFROM2 AND STARTDATE < @DATETO2)' + CHAR(10)

EXEC SP_EXECUTESQL @Query, N'@DATEFROM DATETIME, @DATETO DATETIME,@DTFROM1 DATETIME,
@DATETO1 DATETIME, @DTFROM2 DATETIME, @DATETO2 DATETIME',@aDateFrom = @aDateFrom, @aDateTo = @aDateTo,
@DTFROM1 = @DTFROM1, @DATETO1 = @DATETO1, @DTFROM2 = @DTFROM2,
@DATETO2 = @DATETO2

Kindly help me out from this.

Thanks in advance.

Thanks

Jack

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 10:14:33

I'm fairly sure you've got some typos in there. Also -- what's with all the + CHAR(10)? it just makes the code really hard to read. No need for em in SQL SERVER.

Your code with some formatting. I've highlighted things missing / wrong? in red. In particular the @query should be NVARCHAR(MAX) and you seem to have an extra bracket (or missing a bracket?).



DECLARE
	@DATEFROM	DATETIME,
	@DATETO		DATETIME, 
	@DTFROM1	DATETIME,
	@DTFROM2	DATETIME,
	@DATETO1	DATETIME,
	@DATETO2	DATETIME,
	@Query		NVARCHAR(MAX)

SET @query = N'
SELECT XMLCOLUMN
FROM
	TABLE_A
WHERE
	(
		STARTDATE >=  @DATEFROM
		AND STARTDATE < @DATETO
	)
	OR (
		STARTDATE < @DATEFROM
		AND DOCUMENTENDDATE >=  @DATEFROM
		)
	)'

IF (@DTFROM1 IS NOT NULL AND @DATETO1 IS NOT NULL)  SET @Query = @Query + '
	AND NOT (STARTDATE >= @DTFROM1 AND STARTDATE < @DATETO1)'

IF( @DTFROM2 IS NOT NULL AND @DATETO2 IS NOT NULL) SET @Query = @Query + '
	AND NOT (STARTDATE >= @DTFROM2 AND STARTDATE < @DATETO2)'

EXEC sp_executeSql
	@query
	, N'@DATEFROM DATETIME
	, @DATETO DATETIME
	, @DTFROM1 DATETIME
	, @DATETO1 DATETIME
	, @DTFROM2 DATETIME
	, @DATETO2 DATETIME'
	, @aDateFrom = @aDateFrom
	, @aDateTo = @aDateTo
	, @DTFROM1 = @DTFROM1
	, @DATETO1 = @DATETO1
	, @DTFROM2 = @DTFROM2
	, @DATETO2 = @DATETO2

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

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-03-09 : 10:21:39

Sorry ... i missed those things...

can u plz help on me on this?

Thanks

Jack

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 10:29:48

Why not just add your two conditions to the dynamic SQL just like you have for:


IF (@DTFROM1 IS NOT NULL AND @DATETO1 IS NOT NULL)  SET @Query = @Query + '
	AND NOT (STARTDATE >= @DTFROM1 AND STARTDATE < @DATETO1)'

IF( @DTFROM2 IS NOT NULL AND @DATETO2 IS NOT NULL) SET @Query = @Query + '
	AND NOT (STARTDATE >= @DTFROM2 AND STARTDATE < @DATETO2)'

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

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-03-09 : 10:36:19

That is the requirement i got. i need to check the condition first. if the condition is 'TRUE' then need to eliminate the data between those two dates.

same way i need to check the other conditoin also.

Thanks

Jack

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 01:58:53

Can you do some performance testing and post the results?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources