How to choose which WHERE statement

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

Transact-SQL (2000)

How to choose which WHERE statement

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-14 : 15:48:17

Hi,

Just a quick one, I would like to write a stored procedure which allows the user to pass in a char which is used to define which WHERE statement to use.

I've looked around the web and have read articles about using Dynamic SQL and it seems a big no no, but is there any way I can do it without an IF statement? I have tried using a CASE statement but that doesn't work.

Any suggestions welcome

Tim

ps here's how I've been trying:


DECLARE @WHERE As nvarchar
SET @WHERE = 'F'

SELECT * FROM TSD_DomainNames LEFT JOIN TSD_Clients ON TSD_DomainNames.ClientID = TSD_Clients.ClientID
WHERE CASE @WHERE
  WHEN 'F' THEN DExpiresOn > GETDATE()
  ELSE DExpiresOn < GETDATE()
END
ORDER BY CName ASC, DUURL DESC

Throws the error:

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '>'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-14 : 15:54:56

Use COALESCE for a dynamic WHERE clause:

http://www.sqlteam.com/item.asp?ItemID=2077

Tara

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-14 : 16:01:54

Hi Tara,

thanks for your reply, I did look at that but I think its different to what I'm trying to do, I want to just pass in 1 parameter ie @WHERE which then decides which WHERE to use, ie:

F = DExpiresOn > GETDATE() -Future
P = DExpiresOn < GETDATE() -Past
T = DExpiresOn = GETDATE() -Today
etc

Or am I missing the point?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-14 : 16:18:20

CASE evaluates to a single expression. You accomplish what you want with something along these lines:

where case
when @WHERE = 'F' and DExpiresOn > GETDATE() then 1
when @WHERE = 'P' and DExpiresOn < GETDATE() then 1
when @WHERE = 'T' and DExpiresOn = GETDATE() then 1
else 0 end = 1

I'm using the CASE to produce either a 1 or a 0 and then testing that value.

One other thing to be aware of with your code. The GETDATE() returns both the date and time together. This means that your DExpiresOn comparisons will not evaluate as you might think.

There are a few techniques for (essentially) stripping the time portion off. Look up "Date and Time Functions" in BOL for some details.

HTH

=================================================================

Happy Holidays!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 16:30:47

Since this is inside a stored procedure and you don't want to use Dynamic SQL, then you could just have a series of If-Thens...

IF @WHERE = 'F' BEGIN
SELECT .... WHERE DExpiresOn > GETDATE()
END ELSE IF @WHERE = 'P' BEGIN
SELECT ... WHERE DExpiresOn < GETDATE()
END ELSE BEGIN
SELECT ... WHERE DExpiresOn = GETDATE()
END

-----------------------------------------
Professional IT Solutions from Infoneering

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-14 : 16:32:22

Sorry, I think I'm confusing people I'll just revert to a good old IF statement...


DECLARE @WHERE As nvarchar
SET @WHERE = 'F'

IF @WHERE = 'F'
  BEGIN
    SELECT * FROM TSD_DomainNames LEFT JOIN TSD_Clients ON TSD_DomainNames.ClientID = TSD_Clients.ClientID
    WHERE DExpiresOn > GETDATE()
    ORDER BY CName ASC, DUURL DESC
  END
ELSE
  BEGIN
    SELECT * FROM TSD_DomainNames LEFT JOIN TSD_Clients ON TSD_DomainNames.ClientID = TSD_Clients.ClientID
    WHERE DExpiresOn < GETDATE()
    ORDER BY CName ASC, DUURL DESC
  END

My only worry is that when wanting several different options the Stored Proc will get very large

on well.

Thanks for your help.

Tim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-14 : 16:39:01

use proper boolean algebra.

IF a THEN b

can be expressed in terms of ANDs and ORs and NOTs as:

(Not a) or B

To use Busta's example, if you wanted something like this:

when @WHERE = 'F' then DExpiresOn > GETDATE()
when @WHERE = 'P' then DExpiresOn < GETDATE()
when @WHERE = 'T' then DExpiresOn = GETDATE()

you would write your WHERE clause as:


WHERE (@WHERE<>'F' or DExpiresOn > GetDate()) AND
      (@WHERE<>'P' or DExpiresOn < GetDate()) AND
      (@WHERE<>'T' or DExpiresOn = GetDate())

exactly the same logic. Work it out on paper if you need to. Never put a CASE in WHERE clause if you can avoid it.

Aren't there any computer science or math majors out there working in computers these days???

- Jeff

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-14 : 16:46:44

Ah sorry, I'm with you, thanks. Thats what happens when you try working at home after a days work hehe.

Thanks to one and all, have a merry Christmas

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-14 : 17:39:18

I should just add to this that I've also added "AND (@Type='F' or @Type='P' or @Type='T')" to the end of this to ensure that no records are returned if the input is incorrect etc

Thanks again.

Tim

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-14 : 18:07:54

Well done, sir.

It appears that the University of Birmingham has a better math department than MIT...

;-)

HTH

=================================================================

Happy Holidays!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources