Case in Where clause or select

Question

Where
Case when  CompanyID = 1 ) and  Delete = 0 
 and PersonID in ( Select Personid from Deals where DateRemoved is NULL and 
 DocumentCenterAccessLevelID is not NULL ) Then '1' else '0' end as Priority1
 where Priority1=@Priority1

My logic behind this is can i either use this in my where clause and select clause.. I basically want to see the result of my select stmt based on a parameter which will be my where clause.. i have about 4 conditions and i want all those 4 conditions to be my parameters...

I know my logic in the code doesnt make sense but i need to good start..

---

FM

Answer 1

try :

Where
Case when  CompanyID = 1  and  [Delete] = 0 
 and PersonID in ( Select Personid from Deals where DateRemoved is NULL and 
 DocumentCenterAccessLevelID is not NULL ) Then '1' else '0' end =@Priority1

---

Best regards

Answer 2

What do you mean by "i want all those 4 conditions to be my parameters..."

How many parameters do you have. If all the four conditions meet then you get 1 else 0

• If your input is 1 then all conditions when true will give you output.

• When your input is 0 and any condition is false will give you output.

• If your input is not 1 or 0, you will never get output.

When possible use CTE with UNION ALL for better performance rather than case statements in where clause where ever possible.

Please provide more details.

---

Help the Community know the status of the thread.

Please use Mark as Answer/Propose as Answer if the post solved the problem.

Use Vote As Helpful if the post is inline or hints towards the solution.

- Arun Kumar Allu

Answer 3

I get an error with your sample as Must declare teh scalar variable "@Priority1"

---

FM

Answer 4

I get an error with your sample as Must declare teh scalar variable "@Priority1"

---

FM

You must declare your var :

declare @Priority1 char(1)

or use CONSTANTE

---

Best regards

Answer 5

Arun,

This is what i am looking for , i have 3 queries with same columns in select stmt but diff where clauses so i am trying to create a report in SSRS which will give me results based on parameter. So my idea was to take each  where clause and convert it into a parameter with a value 1=yes and no =0. So when a user want to use thsi report they go and run this report by entering 1 or 0 for either parameter... Does that make sense?

select a,b,c,d,e from my table

where a=cat

2

select a,b,c,d,e from my table

where b not in (elephant)

3

select a,b,c,d,e from my table

where <> cat

---

FM

Answer 6

Arun,

This is what i am looking for , i have 3 queries with same columns in select stmt but diff where clauses so i am trying to create a report in SSRS which will give me results based on parameter. So my idea was to take each  where clause and convert it into a parameter with a value 1=yes and no =0. So when a user want to use thsi report they go and run this report by entering 1 or 0 for either parameter... Does that make sense?

select a,b,c,d,e from my table

where a=cat

2

select a,b,c,d,e from my table

where b not in (elephant)

3

select a,b,c,d,e from my table

where <> cat

---

FM

Read this thread. I have just proposed an answer on this very same requirement with SSRS.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ca6d1447-48a9-4b62-b840-fd11d3a60d73

Brief

• With the above provided suggestion create the appropriate SQL query
• Make it an SP with the required parameters
• Call the SP with your UI parameter you have at runtime.
• How to call a SP in SSRS : http://www.codeproject.com/Articles/20540/Using-StoredProcs-with-Parameters-in-SQL-Reporting
• You get your required output from SQL Server to Reports manager
• Your report shows the output

Hope this is helpful. Let me know if any issues.

---

Help the Community know the status of the thread.

Please use Mark as Answer/Propose as Answer if the post solved the problem.

Use Vote As Helpful if the post is inline or hints towards the solution.

- Arun Kumar Allu

Answer 7

A simple CTE example with UNION ALL.

;WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice) AS
(
   SELECT
      p.ProductName,
      c.CategoryName,
      p.UnitPrice
   FROM Products p
      INNER JOIN Categories c ON
         c.CategoryID = p.CategoryID
   WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
where CategoryName = 1
UNION ALL
SELECT *
FROM ProductAndCategoryNamesOverTenDollars
where CategoryName = 2

Hope this helps.

---

Help the Community know the status of the thread.

Please use Mark as Answer/Propose as Answer if the post solved the problem.

Use Vote As Helpful if the post is inline or hints towards the solution.

- Arun Kumar Allu