CASE Statement using different fields
Hello all,
I have several queries that have been written and copied over the years to conform with the different request. Each query is almost the same just a different where statement. For example one query may need to pull by Admit Date while the next Query needs to pull by the Discharge Date. This data is stored in two different fields So in my WHERE clause I would like to have something like.
My goal is to have one Stored Procedure that can run all these different querys. I am using SQL 2014
Thanks,
Rodger
I have several queries that have been written and copied over the years to conform with the different request. Each query is almost the same just a different where statement. For example one query may need to pull by Admit Date while the next Query needs to pull by the Discharge Date. This data is stored in two different fields So in my WHERE clause I would like to have something like.
SELECT *
FROM PatientVisit AS pv
WHERE CASE @DateType
WHEN 1 THEN pv.VisitStartDate = '01/01/207'
WHEN 2 THEN pv.VisitEndDate = '01/01/2017'
ELSE
END
AND pv.PatientType = 'I'My goal is to have one Stored Procedure that can run all these different querys. I am using SQL 2014
Thanks,
Rodger
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Don't do that, because you'll get inefficient query plans.
Either:
1) Use dynamic SQL
2) Use IF statements and more customized queries:
IF @DateType = 1
SELECT *
FROM PatientVisit AS pv
WHERE pv.VisitStartDate = @Date
AND pv.PatientType = 'I'
ELSE
IF @DateType = 2
SELECT *
FROM PatientVisit AS pv
WHERE pv.VisitEndDate = @Date
AND pv.PatientType = 'I'
ELSE
...etc...
Either:
1) Use dynamic SQL
2) Use IF statements and more customized queries:
IF @DateType = 1
SELECT *
FROM PatientVisit AS pv
WHERE pv.VisitStartDate = @Date
AND pv.PatientType = 'I'
ELSE
IF @DateType = 2
SELECT *
FROM PatientVisit AS pv
WHERE pv.VisitEndDate = @Date
AND pv.PatientType = 'I'
ELSE
...etc...
ASKER
Is that the best way as then I have to support two queries at that point . . . . .it would be the same as using two stored procedures. One for Admit Date and one for Discharge date. I was looking for a way to have one select statement with the fields that I need and then a dynamic where statement.
I was looking for a way to have one select statement with the fields that I need and then a dynamic where statement.You can do that, but the performance will be terrible for at least some of the queries. Keep in mind that SQL has to build one plan that handles every execution of that query. Therefore, for things like (WHEN x AND y) OR (WHEN z AND aa), SQL can't use any indexes or other performance techniques it simply has to fully scan the table(s) involved.
Dynamic SQL would get rid of the problem of maintaining separate queries, but it does have some security implications, since it requires the user running the code have direct access to the tables and it is subject to sql injection.
ASKER
OK I will look at that then.
I am creating a front end for the end users to select a report that they would like to run. I am giving them options on how to run the report. Like I said most of the time they would be changing the type of date (admitting, discharge, posting, entry, etc . . ) they are looking for or the type of physician (admitting, attending, primary, procedure, etc . . .) All of my connections to the database are in the back end code the users will not see. I am also going to look at scheduling some of these to run as they need them at month end or some daily.
They system is still being upgraded, looks like I will be able to get in tomorrow so I will try it out and let you know.
I am creating a front end for the end users to select a report that they would like to run. I am giving them options on how to run the report. Like I said most of the time they would be changing the type of date (admitting, discharge, posting, entry, etc . . ) they are looking for or the type of physician (admitting, attending, primary, procedure, etc . . .) All of my connections to the database are in the back end code the users will not see. I am also going to look at scheduling some of these to run as they need them at month end or some daily.
They system is still being upgraded, looks like I will be able to get in tomorrow so I will try it out and let you know.
Rodger, were you able to perform the necessary tests for this question?
Please let us know if you need further assistance or if this question can be closed.
Cheers
Please let us know if you need further assistance or if this question can be closed.
Cheers
ASKER
That is just what I was looking for I was able to get the query to preform just as I wanted using this code.
ASKER