Report giving the following error if I select multiple values for one parameterAn error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'Main_Query'. Incorrect syntax near 'C'. Must declare the scalar variable "@SQL". It is working fine if i select single single values for different parameters but when i select multiple values the above error is comingPlease help me Naresh

Hi To my understanding of your scenario with provided error message, 1. If your query is based on TSQL Please check the main query where clause and use IN (@SQL) 2. If your query is based on MDX Please use STRTOSET(@SQL) In think, your using TSQL source query. Prav

My query is on above,help me,why I am getting error.Naresh

Hi There Have you embedded this query inside reporting end or you are running from stored procedure Please reply Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

I run the above query in the report onlyNaresh

Hi, I think your proc need split string funtion to pass multivalue so the value can be passed in comma seprated rather scaler single value . Go to this link How to pass multivalue Hope this will help you !!! Sanjeewan

In my query I'm not using Stored procNaresh

After the above query just I'm selecting from temp tableNaresh

Hi There I am not sure why you are using dynamic SQL but can you please instead of this AND cpm.status in ("'+@Card_status+'") and c.status in ('+@Cust_status+') Please try this by putting (@Card_status) AND cpm.status in ("'+(@Card_status)+'") and c.status in ("'+(@Cust_status)+'") Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Here I'm passing parameters into openquery that is the reason I'm using AND cpm.status in ("'+@Card_status+'") and c.status in ('+@Cust_status+') I tried (@Card_status),but no use,any alternative method?Naresh

Hi, Go to dataset property then click on Parameter and pass the parameter value like this =join(Parameters!<your param name>.Value,",") http://lukehayler.com/2009/08/the-join-function-in-ssrs/ Hope this will help you !!! Sanjeewan

not workingNaresh

Hi There please try this as I am thinking openquery might need string in quotes declare @Card_statusNew varchar(4000) set @Card_statusNew= replace(@Card_status, ',', ''',''') AND cpm.status in ("'+@Card_statusNew+'") and c.status in ("'+@Card_statusNew+'") Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful declare @Card_statusNew varchar(4000) set @Card_statusNew= replace(@Card_status, ',', ''',''') AND cpm.status in ("'+@Card_statusNew+'") and c.status in ("'+@Card_statusNew+'")

I will share my screen through skype,could u please help me.Naresh

Hi There Please give a try to this as well as Sanjeewan said Go to dataset property then click on Parameter and pass the parameter value like this =join(Parameters!<your param name>.Value,",") DECLARE @Card_statusNew AS varchar(4000) SET @Card_statusNew = REPLACE(@Card_status, ',', ''',''') SET @Card_statusNew = REPLACE(@Card_statusNew , '' + '''' + '', '' + '''''' + '') AND cpm.status in ( ''' + '''' + @Card_statusNew + '''' + ''') and c.status in ( ''' + '''' + @Card_statusNew + '''' + ''') Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful =join(Parameters!<your param name>.Value,",") DECLARE @Card_statusNew AS varchar(4000) SET @Card_statusNew = REPLACE(@Card_status, ',', ''',''') SET @Card_statusNew = REPLACE(@Card_statusNew , '' + '''' + '', '' + '''''' + '') AND cpm.status in ( ''' + '''' + @Card_statusNew + '''' + ''') and c.status in ( ''' + '''' + @Card_statusNew + '''' + ''')

Can any one help me pleaseNaresh

Hi There Please give a try to this as well as Sanjeewan said Go to dataset property then click on Parameter and pass the parameter value like this =join(Parameters!<your param name>.Value,",") DECLARE @Card_statusNew AS varchar(4000) SET @Card_statusNew = REPLACE(@Card_status, ',', ''',''') SET @Card_statusNew = REPLACE(@Card_statusNew , '' + '''' + '', '' + '''''' + '') AND cpm.status in ( ''' + '''' + @Card_statusNew + '''' + ''') and c.status in ( ''' + '''' + @Card_statusNew + '''' + ''') Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful =join(Parameters!<your param name>.Value,",") DECLARE @Card_statusNew AS varchar(4000) SET @Card_statusNew = REPLACE(@Card_status, ',', ''',''') SET @Card_statusNew = REPLACE(@Card_statusNew , '' + '''' + '', '' + '''''' + '') AND cpm.status in ( ''' + '''' + @Card_statusNew + '''' + ''') and c.status in ( ''' + '''' + @Card_statusNew + '''' + ''')

U have Skype ?Naresh

Hi There No Sorry I dont have Is it not working still ? Please reply Thanks Syed

it says the replace function requires 3 argumentsNaresh

Give me ur gmail id please,then we can easily chat over there.Naresh

Hi There you might be doing something else please see attached pic DECLARE @Card_statusNew AS varchar(4000) declare @Card_status as varchar(100) set @Card_status='syed,Rick,Andrew' SET @Card_statusNew = REPLACE(@Card_status, ',', ''',''') SET @Card_statusNew = REPLACE(@Card_statusNew , '' + '''' + '', '' + '''''' + '') print @Card_statusNew

Hi There my gmail id is sqazafi@gmail.com Regards Syed

Hi Naresh Thanks for your compliments. I am really glad that your problem has been resolved. Good luck for your project Many thanks Syed Qazafi Anjum

Hello, Multi-value parameters are passed to SQL stored procedure as a comma-delimited string of the values. We need to parse this back into values in our stored proc. Please refer to the following articles to deal with this issue: Passing multi-value parameter in stored procedure ssrs: http://social.msdn.microsoft.com/Forums/sk/sqlreportingservices/thread/0ead7ceb-3fdd-4625-aa82-1d4195f984b1 Passing multi-value parameter in stored procedure: http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/ Regards, Bin Long Bin Long TechNet Community Support

Hi Bin This issue is not related to SQL Server Stored procedure. In reality I think he is using OPENQUERY to get the values from Oracle and passing multivalued parameter Many thanks Syed Qazafi Anjum

Thank you Very much SYED It really helpful I don't forget you I'm adding you in my gmail(nareshanumolu@gmail.com)Naresh