Link to home
Start Free TrialLog in
Avatar of mauralartin
mauralartin

asked on

Need help with string syntax in dynamic SQL statement

Urgent Need - So much so that I rejoined Expert-Exchange! Was a memebre for 6 years.

Can anyone PLEASE see what is wrong with this string. I am doing a report in SSRS. This is in a dataset that is feeding a matrix report with LOTS of inScope() functions.

   WHERE Month(" & Parameters!TimeSlicer.Value & ") IN (" & Join(Parameters!Month.Value,",") & ")" & "
                  AND Year(" & Parameters!TimeSlicer.Value & ") IN (" & Parameters!Year.Value & "," & Parameters!Year.Value -1 & ")


I've pasted it into management studio, and printed it but do not see the missing single apostrophes
string-in-dynamic-SQL.docx
ASKER CERTIFIED SOLUTION
Avatar of plummet
plummet
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mauralartin
mauralartin

ASKER

Thanks John, I know that is the case when the dynamic query is within a procedure being run in SQL Management Studio but for some reason I am thinking it is different in Visual Studio BIDS. I'll try the single quote chr(') in the passing first in BIDS and then try passing to a procedure. Thanks
Hi again

I've checked back and the SQL query should use the parameters like a normal SQL variable, ie

WHERE Month(@TimeSlicer) IN (@Month)

Though be careful with the parameters you've called Month and Year as they are reserved SQL words. I think they will work but I'd always avoid that sort of thing and use something like repMonth, etc.

I should have remembered all this earlier but I haven't used SSRS for a few months!

I hope that helps you fix the problem

Regards
John
I meant to mark the other solution as accepted