Is it possible to put in "IF ...ELSE" or "Case" in WHERE CLAUSE?

Dear all...

need your help... i am now trying to create a report using SQL reporting services... when declare all the @.parameters needed in where clause, i have come across a problem. where one of the parameters that prompting user to key in...i need to put in some condition.

select..............(blah blah).....
.....(SELECT CASE WHEN
((SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID)) = 1 THEN 0 ELSE 1 END) AS PTType .....................(blah blah)......

where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate) OR (@.FromDate = ' ') OR (@.ToDate = ' ')
AND (@.PatientType = CASE WHEN
(SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0 ELSE 1 END)

my situition is something like above, i know i have done something wrong in teh WHERE clause for the @.PatientType... can i ask how to restrict the parameters entered by user, let's say if user enter parameter "0", then the visitcount is 1, if enter "1" then the visit count refers to more than 1...

thanks in advanced ...............Your post is kind of confusing regarding requirements, but part of your problem may be due to using brackets where they are not necessary, and not using them where they might be necessary to specify logical operations.

--Your Version:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate)
OR (@.FromDate = ' ')
OR (@.ToDate = ' ')
AND (@.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END)

--Unnecessary brackets removed:
where CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' '
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END

--Useful brackets added:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' ')
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END|||ya...thanks for reminding me...as there are too many parameters to pass, i also confused... ;) anyway, really appreciate ur help

is it possible to have variant condition clause in procedure?

i want to use OLEDB to build a COM for my app

in the case, i want to execute a select statement which the where-clause is variant.

ex,

select * from db1 where code='abc'

select * from db1 where name='mike'

As it's very difficult to change sql-command in oledb, i want to build a procedure like this,

create procedure viewDB
@.filter CHAR(20)

as

select * from db1 where @.filter

go

but failed!

i tried EXEC(select), but i cant get the variants when building a oledb consumer

No, you cannot pass the whole WHERE clause as a parameter. You could pass only values. In your case if this is predefined set of the types of conditions, then you could create additional parameter in your SP and pass type of the condition there. then, inside of SP, first check type of the query using IF statement and based on it call specific SQL

IF @.MyTYPE='A'

select * from db1 where code=@.filter

ELSE

select * from db1 where name=@.filter

|||

oh, yeah! why i didnt come up with this smart idea, haha

but i found a better solution, bind the columns manually!

|||What is that? Is it constracting SQL statement dinamically inside of SP?|||

no, it's not that!

is easy!

build a SP like this:

create procedure test
@.p char(40)
as
exec( 'select * from testdb where ' + @.p )
go

when executing this sp in sql-server, we can get the columns correctly! but, when using the ole-wizard to build a oledb consumer , the columns disappear. the fact is, the columns lay there steadily, it's ole-wizard didn't bind the columns for us, haha

so let's DIY

|||

But this is worst way to do. It is a pure SQL injection. If I pass next string in your parameter then it will be executed with the different result

Assuming I am passing next value in a parameter

1=1; SHUTDOWN --

Then it will execute your SELECT and then it will shutdown server completely. I could execute DELETE statement or something else. This is how hakers could get control of your server

|||

oh, thanks for telling me that!

but, what if i remove the string after the semi-colon?

my plan is to create a procedure, and use a ATL oledb consumer to access it, if i dont expose the SP name, i think the hackers wont hack me this way

|||They do not need to know your SP name. All the nee to do is to pass value like that to the parameter from your application and job is done. For example, if your screen accepts input for the parameter from outside then screen will accept this value and code will be executed. Another drawback of the dynamic SQL is that it is slower. It means your SP will be recompiled each time when you call it and new execution plan will be prepared.|||

really thanks this piece of infomation!

so, 'select * from table where col=@.p' is safe right?

ok, i will try to re-code my SP

|||

i fond it is almost impossible to code my SP like you suggestted, because my condition clause is so complicate.

i figured out this new plan, and i want to get some advice from you, thx

create procedure proc
@.p1 varchar(10),
@.p2 varchar(10),
...
@.pn varchar(10)

as

if @.p1 is not null
begin
select * into retTable from table where col1= @.p1
--select * into tmpTable from table where col1= @.p1
end
if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2=@.p1
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

if @.p3 is not null
begin
if object_id('tmpTable') is not null
drop table tmpTable
select * into tmpTable from retTable where col3=@.p3
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

...
...

go

BUT, i wonder if it's efficiency !

could you give me some suggestion or a better solution, thx

|||

I see that code for the second and third IF statements is the same. Does it mean that it suppose to be something like below? It is not an actual code that could work, but shows an idea. If idea is correct then you could pass array of values as one parameter into stored procedure using XML string and then use it inside of the IN clause. If this is what you need, then I will post a code that shows how to pass arrfay of values into SP and how to use it there

if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2 IN (@.p1, @.p3)
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

|||

well, it should be 'col3' in the 3rd IF statement

so, u mean my idea wont work actually right?

what do u mean pass a parameter using XML string? how can i parse it in the SP?

|||

Here is my article about how touse XML to pass array of values into SP.

http://support.microsoft.com/kb/555266/en-us

I will try to think about ideas how to do this in your case and will let you know

|||

now i have a problem about injection attack!

could you tell me if the following code safe.

procedure sp_a
@.p CHAR(40)
as
select * from tab1 where col1=@.p
go

|||Yes, it is safe if you do not do anything else inside if this SP. Just small suggestion. If you can, select just the fields you need and avoid using *. It will impove performance

Is database username case sensitive?

If I define a user as 'foo' in a database, are 'Foo','FOO' and other f,o,o
conbinations all the same user?
Thanks,
Bing
as far as I know, If you selected a case-sensitive sort order when you
installed SQL Server, your login ID is also case-sensitive.
"bing" wrote:

> If I define a user as 'foo' in a database, are 'Foo','FOO' and other f,o,o
> conbinations all the same user?
> Thanks,
> Bing

Is database username case sensitive?

If I define a user as 'foo' in a database, are 'Foo','FOO' and other f,o,o
conbinations all the same user?
Thanks,
Bingas far as I know, If you selected a case-sensitive sort order when you
installed SQL Server, your login ID is also case-sensitive.
"bing" wrote:
> If I define a user as 'foo' in a database, are 'Foo','FOO' and other f,o,o
> conbinations all the same user?
> Thanks,
> Bing

Is database username case sensitive?

If I define a user as 'foo' in a database, are 'Foo','FOO' and other f,o,o
conbinations all the same user?
Thanks,
Bingas far as I know, If you selected a case-sensitive sort order when you
installed SQL Server, your login ID is also case-sensitive.
"bing" wrote:

> If I define a user as 'foo' in a database, are 'Foo','FOO' and other f,o,o
> conbinations all the same user?
> Thanks,
> Bing

is case statement the only way

Hi

I need to generate a SQL report like below,its basically calculating the count of students

For

District Level

then for

Region Level

then for

Each School under a Region

Like the Display Below

District summary

Total

Male

Femal

Indian

White

Asian

--

--

--

Type1

22

33

22

11

11

11

23

11

13

Type2

2

…6

…7

;;;;

13

14

Region 1

Region 2...................Region 8

Each School

School1....School 15

Do I have to have

case for each Type for each Race

and then

for Each Levels

of

District

Region

And 500 Schools?

Please Help

Thanks

Could you post your DDL statements and some sample data? This will help us understand your problem and hopefully provide a solution....
|||

From what I understand you may be looking for the PIVOT operator which is available in SQL 2005.

http://technet.microsoft.com/en-us/library/ms177410.aspx

|||

SELECT distinct SCHOOL_REGION,SCHOOL_NUMBER,ETHNICITY,SCH.S_SCHL_NAME,

INTV.Intervention_ ID,

case

INTV.Intervention_ ID when '1' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

case

INTV.Intervention_ ID when '2' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

........

-

-

case

INTV.Intervention_ ID when....... '14' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

FROM STUDENT_DEMOGRAPHIC STDM

left join school_location SCH on STDM.school_number = SCH.S_SCHOOL_NUM

left join Meeting MTNG on STDM.student_id = MTNG.student_id

left join Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID

left join Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID

where INTV.Intervention_ID not in ('')

group by SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

ORDER BY SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

P.S ...here the inner case statement is for the horizontal column names in the report

and I need the totals for the Type 1 ..type2 ...type 14 which is my vertical column in my report ( the INTV.Intervention_ID is this field) which i use in outer case statement above