Change TSQl statement using case

Hello all, Thanks in advance to contributions.
I would like to be able to manipulate myTSQL in a stored procedure, based
on a value in a parameter, using the case statement.
I want to avoid Begin..End constructs
My code is such...
select field1,field2 etc etc
from Order o
inner join tablex oa on o.order_id = oa.order_id
-- etc etc p to 8 joins
where
-- some predicate
-- then the bit I want to case
case @.MyParameter
when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
when 'DE_MANAGER' then (and o.reason = 'T3')
end
So, what I am trying to do is change the overall selection predicate based
on the
value of the @.MyParameter. I am trying to avoid wrapping the whole code
block up into seperate Begin..End constructs if possible as it will make the
SP very big and ugly.
Amy I trying to break SLQ rules here?
Cheers
Scotchy
eg.Scotchy wrote:

> So, what I am trying to do is change the overall selection predicate
> based on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole
> code block up into seperate Begin..End constructs if possible as it
> will make the SP very big and ugly.
> Amy I trying to break SLQ rules here?
That is indeed not possible. You have the same problem as a couple of
threads above, check "Stored procedure using dynamic WHERE statement"
for the offered solutions.
HTH,
Stijn Verrept.|||yep, I was clutching a straws and hoping :-)
cheers
"Stijn Verrept" wrote:

> Scotchy wrote:
>
> That is indeed not possible. You have the same problem as a couple of
> threads above, check "Stored procedure using dynamic WHERE statement"
> for the offered solutions.
> --
> HTH,
> Stijn Verrept.
>|||declare @.parm int
declare @.sql nvarchar(400)
set @.parm = 1
set @.sql = 'select * from table1 where Type IS NOT NULL and '
if ( @.parm = 1 )
set @.sql = @.sql + 'type = ''t1'''
else if ( @.parm = 2 )
set @.sql = @.sql + 'type = ''t2'''
else
print 'error'
exec (@.sql)
William Stacey [MVP]
"Scotchy" <Scotchy@.discussions.microsoft.com> wrote in message
news:5CB3CA78-0973-4ED8-B308-7B3028CBB8F2@.microsoft.com...
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make
> the
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||-- Couple of things: do you need to CASE? Can't you do something like this
?
USE Northwind
GO
DECLARE @.EmployeeID INT
SET @.EmployeeID = 2
-- Normal query
SELECT *
FROM orders
WHERE EmployeeID = @.EmployeeID
-- You can use CASE in queries. Think of them like a function which returns
a value, so you have to put it on one side of an equals:
SELECT *
FROM orders
WHERE CustomerID =
CASE @.EmployeeID
WHEN 1 THEN 'ERNSH'
WHEN 2 THEN 'BLONP'
ELSE ''
END
-- Or multiple criteria. You could even nest your case statements.
SELECT *
FROM orders
WHERE CustomerID =
CASE
WHEN @.EmployeeID = 1 AND OrderDate < '19970101' THEN 'ERNSH'
WHEN @.EmployeeID = 2 AND OrderDate < '19970101' THEN 'BLONP'
ELSE ''
END
-- Let me know how you get on.
--
--
-- Damien
"Scotchy" wrote:

> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make t
he
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||On Tue, 20 Dec 2005 18:07:01 -0800, Scotchy wrote:

>Hello all, Thanks in advance to contributions.
>I would like to be able to manipulate myTSQL in a stored procedure, based
>on a value in a parameter, using the case statement.
>I want to avoid Begin..End constructs
>My code is such...
>select field1,field2 etc etc
>from Order o
>inner join tablex oa on o.order_id = oa.order_id
>-- etc etc p to 8 joins
>where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
>So, what I am trying to do is change the overall selection predicate based
>on the
>value of the @.MyParameter. I am trying to avoid wrapping the whole code
>block up into seperate Begin..End constructs if possible as it will make th
e
>SP very big and ugly.
>Amy I trying to break SLQ rules here?
Hi Scotchy,
CASE is an expression, not a statement. Check the examples in Books
Online to get a feeling for what you can accomplish with CASE. And never
think that CASE in SQL Server is similar to CASE in languages such as C.
For your situation, a combination of OR and AND works better than a
CASE:
WHERE ...
AND ( (@.MyParameter = 'IS_MANAGER' AND o.reason IN ('T1', 'T2') )
OR (@.MyParameter = 'DE_MANAGER' AND o.reason = 'T3' ))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)