CASE in WHERE clause?

I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:

select name, address, city etc
from customer
where if users want to see customers of a certain type then type = @.myInParameterForType
and if users want to see customers from a certain area then area = @.myInParameterForArea

is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!Yes, you may use CASE in where|||one easy (but not very performant) way is to build your "where" clause in your application (ie:web form ) and send it to your sp:
------------------------
CREATE PROCEDURE mySP

(
@.WhereClause varchar(4000)
)

AS
Declare @.SQL varchar(6000)

begin

set @.SQL = 'SELECT field1, field2 ... from myTable where ' + @.WhereClause + ' ORDER BY someID'

end

EXEC (@.SQL)

RETURN

GO
------------------------

when you have many optional parameter it is very conveniant

Case Conditional in SQL Statement - MS SQL 2000

Hi,

I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.

Here is my code thus far:

select a.DeptCode DeptCode,
a.Type Type,
(a.ExpenseUnit / (select volume from TargetData b where b.type =
a.type)
) Expense
Fromcalc1 a

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement. Even
If statements will work (if that's possible), though case would be
less messy.

Any suggestions would be much appriciative. Thanks...

Alex.Hi

Is it not totally clear how you are joining these tables, but this may be a
start.

SELECT a.DeptCode DeptCode,
a.Type Type,
a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
WHEN a.Type = 'Budget' THEN
c.volume
WHEN a.Type = 'Target' THEN
d.volume
ELSE 1 END ) AS Expense
From calc1 a
LEFT JOIN FYTDData d ON b.type = a.type
LEFT JOIN BudgetData d ON c.type = a.type
LEFT JOIN TargetData d ON d.type = a.type

John

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310010840.5910e221@.posting.google.c om...
> Hi,
> I'm trying to do calculations in a SQL statement, but depending on one
> variable (a.type in example) I'll need to pull another variable from
> seperate tables.
> Here is my code thus far:
> select a.DeptCode DeptCode,
> a.Type Type,
> (a.ExpenseUnit / (select volume from TargetData b where b.type =
> a.type)
> ) Expense
> From calc1 a
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement. Even
> If statements will work (if that's possible), though case would be
> less messy.
> Any suggestions would be much appriciative. Thanks...
> Alex.|||Alex (alex@.totallynerd.com) writes:
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement.

Books Online is a very resource for this kind of information, just
look up CASE. Be careful to notice that this is not a statement, but
an expression.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f7b0da9$0$8765$ed9e5944@.reading.news.pipex.net>...
> Hi
> Is it not totally clear how you are joining these tables, but this may be a
> start.
> SELECT a.DeptCode DeptCode,
> a.Type Type,
> a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
> WHEN a.Type = 'Budget' THEN
> c.volume
> WHEN a.Type = 'Target' THEN
> d.volume
> ELSE 1 END ) AS Expense
> From calc1 a
> LEFT JOIN FYTDData d ON b.type = a.type
> LEFT JOIN BudgetData d ON c.type = a.type
> LEFT JOIN TargetData d ON d.type = a.type
> John

Hi John...

I did get it going yesterday after spending about an hour testing
syntax. Below is the final SQL statement. Works great!

select a.DeptCode,
a.Type,
(((a.TotalPaidHoursUnit/(Case a.type
When 'FYTD04' Then null
When 'Budget' Then (select b.monthly from it_budvol b where
b.deptcode = a.deptcode)
When 'Prior Year' Then (select b.avemonth from it_pyvolume b
where b.deptcode = a.deptcode)
Else (select b.AveMonthVolume from solucient_dss b where
b.deptcode = a.deptcode and b.type = a.type)
end)
- a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE

Fromdss_calc a

Thanks for the feedback.

Alex.|||alex@.totallynerd.com (Alex) wrote in message news:<2ba4b4eb.0310020704.4c08463e@.posting.google.com>...
> Hi John...
> I did get it going yesterday after spending about an hour testing
> syntax. Below is the final SQL statement. Works great!
>
> select a.DeptCode,
> a.Type,
> (((a.TotalPaidHoursUnit/(Case a.type
> When 'FYTD04' Then null
> When 'Budget' Then (select b.monthly from it_budvol b where
> b.deptcode = a.deptcode)
> When 'Prior Year' Then (select b.avemonth from it_pyvolume b
> where b.deptcode = a.deptcode)
> Else (select b.AveMonthVolume from solucient_dss b where
> b.deptcode = a.deptcode and b.type = a.type)
> end)
> - a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE
> Fromdss_calc a
> Thanks for the feedback.
> Alex.
Hi

You should make sure that you are not dividing by zero.

John

Case conditional action

Is it possible to check a variable in a case statement then perform a select
query depending on the variable? What I have tried is something like this
which does not work. aNyone know how this can be accomplished?
declare @.@.var int, @.rtn int
set @.var = 3
select case @.var when 1 select @.rtn = catid from categories
when 2 select @.rtn = itmid from categories
when 3 select @.rtn = catmid from categorymixbjamin wrote:
> Is it possible to check a variable in a case statement then perform a sele
ct
> query depending on the variable? What I have tried is something like this
> which does not work. aNyone know how this can be accomplished?
>
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
A CASE expression is like this:
CASE <condition>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
.. etc. ...
END
Use IF:
IF @.var = 1
SELECT @.rtn = catid FROM categories
IF @.var = 2
SELECT @.rtn = itmid FROM categories
IF @.var = 3
SELECT @.rtn = catmid FROM categorymix
These statements assume that there is only 1 row in each table, which is
probably wrong. What are you really trying to do?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAy4TYechKqOuFEgEQKYbwCgzax4m6K6c5lD
CcRV3sxStCYnhlUAoPRg
pAwpcd7fD1BWfkIGeB21eKuG
=opZI
--END PGP SIGNATURE--|||bjamin (bjamin@.discussions.microsoft.com) writes:
> Is it possible to check a variable in a case statement then perform a
> select query depending on the variable? What I have tried is something
> like this which does not work. aNyone know how this can be
> accomplished?
There isn't any CASE statement in SQL. There is a CASE expression, which is
something different.

> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
Could write:
SELECT @.rtn = CASE WHEN 1 THEN (SELECT catid FROM categories)
WHEN 2 THEN (SELECT itmid FROM categories)
WHEN 3 THEN (SELECT catmid FROM categorymix)
EMD
Although, I think most people would prefer to use IF/ELSE in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

CASE / NULL Problem

Hello!
I want to set a value depending on a condition but I can't seem to get it
right when the original value is NULL.
Select bla, bla... FROM bla...
*****************************
DellyDate=CASE DelivDate
WHEN NULL THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
***********************************'
This code parses successfully, but even when DelivDate actually is NULL,
DellyDate never has the value '1999-12-31'. What am I doing wrong here?You'll need to use IS NULL.
DellyDate=CASE WHEN DelivDate IS NULL
THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
or alternatively
DellyDate=COALESCE(LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) +
'-' +
RIGHT(DelivDate,2) ,'1999-12-31' )|||DellyDate = CASE WHEN DelivDate IS NULL THEN '1999-12-13' ELSE .... END
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Clarkie" <clarkbones@.rock.sendmenot.etmail.com> wrote in message
news:eKVi$lQJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>|||you could also
coalesce(convert(varchar(10), convert(datetme, DelivDate), 120),
'1999-12-31')
"Clarkie" wrote:

> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>
>|||Read the definition of the CASE expression. You are using a shorthand
for
SELECT ..
FROM ..
WHERE delly_date
=CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
THEN '1999-12-31'
ELSE .. END ;|||Thanks for all the answers!
Problem solved!
//Clarkie
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138581718.965490.84740@.g14g2000cwa.googlegroups.com...
> Read the definition of the CASE expression. You are using a shorthand
> for
> SELECT ..
> FROM ..
> WHERE delly_date
> =CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
> THEN '1999-12-31'
> ELSE .. END ;
>