troubleshooting Question

SQL Query variable in Where clause

Avatar of Jimbo99999
Jimbo99999Flag for United States of America asked on
Microsoft SQL ServerMicrosoft Server OSSSRSSQL
6 Comments3 Solutions147 ViewsLast Modified:
Good Day Experts!

I am hoping you can help me understand my options for resolving a dilemma.  I have developed a SQL query that runs fine and the desired output is achieved in ManagementStudio.
A parameter to the query(used to simulate User selection from an SSRS report) is a comma separated list.  Using that comma separated list,  I retrieve values from a table in my database and then build a string.  Here is the output of that string for the 4 comma separated values:

(incidentDate >= '2017-08-17' and incidentDate <= '2017-10-12') OR
(incidentDate >= '2017-10-16' and incidentDate <= '2017-12-19') OR
(incidentDate >= '2018-01-03' and incidentDate <= '2018-03-16') OR
(incidentDate >= '2018-03-19' and incidentDate <= '2018-05-31')

I set this to a variable to use in my query string which is then set to @cmdString. I then exec(@cmdString).  Works great no issues.

The issue I am having is when I try to use this in my SSRS report.  The editor does not recognize the fields to display on the report since they are in the string I am executing.  
Then when I try to create that query the "regular" way, I run into trouble because of trying to use a variable in the Where clause. I am not sure what to do.

Please ask questions as I probably did not explain well,
jimbo99999
ASKER CERTIFIED SOLUTION
Dustin Saunders
Co-Founder and Chief Architect
Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros