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 pass array to stored procedure

Dear All,

I am using sql2000, I want to know whether in stored procedure we can pass

array. Or is there any other solution to pass array of records

Please Guide Me

thanks

Moving SQL Server forum.

Thanks.

|||

No... it is not posible to pass arryas to sp. BTW what is u r requirement , there are workaround for everything.. so pse specify u r requirement..

Madhu

Is Cross-Validation (or Rotation Estimation) available in SQL Server?

Dear forum users,

I am a newbie in using MS SQL server with analysis services.
There seems to be no 'cross-validation' tool in MS SQL
which is frequently used in data mining and even statistics.
Is there anyone having similar difficulties?
Is there any solution like a small scripts to divide
the given dataset with multiple folds?
Your valuable comments and feedbacks would be appreciated.

Minnetongka

We have implemented cross-validation as part of a larger "model evaluation" module on top of the data mining algorithm implementations in SQL-Server 2005 Analysis Services. This is done by implementing sampling on top of the source case/nested tables and then utilizing the Analysis Services APIs (in C#) to train predictive models over training sets and then execute the appropriate prediction join over the testing sets to collect model performance metrics.

We commonly use this "model evaluation" model to automate the process of finding the most accurate predictive models for a given application.

Although we can't make our module publicly available at this time, let me know if you're interested in more information.

Thanks,

- Paul Bradley