Can you vary the Operands in SELECT statement? - 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 2000 Forums

Transact-SQL (2000)

Can you vary the Operands in SELECT statement?

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2005-02-17 : 14:50:35

Is it possible to build the list of operators "dynamically"? In other words, in the WHERE clause I want to vary some of the operators on a case by case basis. Is this possible? I've tried the query below (after many other unsucessful attempts) but still getting an error:
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '='.


SELECT AcctNum, FirstName, LastName, OrderType, ShimpmentType, RepID, TeamID
FROM Customers
WHERE RepID = @pRepID
AND TeamID = @pTeamID
--Start custom and all filters
AND
CASE WHEN @pFilter = 'JSmith' THEN
OrderType = 'New' AND StatusInDays > 7
OR ShipmentType = 'UPS'
WHEN @pFilter = 'Platnum' THEN
OrderType = 'Priority' AND StatusInDays > 2
OR ShipmentType = 'FedEx'
END

thanks,

Cameron

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 17:18:36

you don't need a case for that:


where
(
((OrderType = 'New' AND StatusInDays > 7 OR ShipmentType = 'UPS') and @pFilter = 'JSmith')
OR
((OrderType = 'Priority' AND StatusInDays > 2 OR ShipmentType = 'FedEx') and @pFilter = 'Platnum')
)

Go with the flow & have fun! Else fight the flow

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-02-17 : 19:25:28

Even though the CASE statement here is not as efficient, for future reference if you wanted to use CASE in the WHERE clause, you can use this kind of structure:

...
AND OrderType =
CASE @pFilter
WHEN 'JSmith' THEN 'New'
WHEN 'Platinum' THEN 'Priority'
END
AND ...

CactusJuice
Starting Member

46 Posts

Posted - 2005-02-17 : 20:21:26

Thank you for your reply. This won't work because I really need to vary the operators depending upon whom is calling the web page. I probably didn't explain myself well enough. Every user wants the data filtered differently. @pFilter is just a variable I created to help in the branching logic...to know whom I'm constructing the WHERE clause for. For example if John Smith is requesting the report then he needs to see:


WHERE RepID = @pRepID
AND TeamID = @pTeamID
OrderType = 'New' AND StatusInDays > 7 OR ShipmentType = 'UPS'

And if Platnum team is looking at the data they need to see:


WHERE RepID = @pRepID
AND TeamID = @pTeamID
OrderType = 'Priority' AND StatusInDays > 2 OR ShipmentType = 'FedEx'

There are probably a dozen more different WHERE clauses. And some have more conditions on the data! I just posted the two simplest.

quote:
Originally posted by spirit1

you don't need a case for that:

where
(
((OrderType = 'New' AND StatusInDays > 7 OR ShipmentType = 'UPS') and @pFilter = 'JSmith')
OR
((OrderType = 'Priority' AND StatusInDays > 2 OR ShipmentType = 'FedEx') and @pFilter = 'Platnum')
)

Go with the flow & have fun! Else fight the flow

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-17 : 20:26:19

The pattern Spirit is showing you will work for what you are trying to do.. Do you understand the logic he is applying?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-02-18 : 06:58:30

remember that when mixing AND's and OR's....it's best to use brackets to ensure you get the correct results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources