Why is report builder showing error: Incorrect syntax near '@var_1'

• SqlServer300

  Mr or Mrs. 500

  Points: 536

  More actions

  May 11, 2017 at 9:45 am

  #402051

  I am using "Microsoft SQL Server Report Builder 3.0" on "SQL Server Reporting Services 2012".

  From a main report main.rdl I am calling a drillthrough report detail.rdl

  The main.rdl report has a table and each text box of this table is clickable and drillthrough action. When user clicks on any text box the detail.rdl report is called. One parameter that is passed to detail.rdl report is used as a column name in the SQL. This SQL fetches data needed for detail.rdl report. 

  The SQL is:
  DECLARE @SQL varchar(max)

  SET @SQL =
'SELECT
[table_name].[col_5]
,[table_name].[col_13]
,[table_name].[col_4]
,[table_name].[col_21]
,[table_name].[col_9]
,[table_name].[col_28]
,[table_name].[col_11]
,' + @column + /*the value in this variable is an existing column name from table table_name*/
' FROM
[table_name]
WHERE
([table_name].[col1] LIKE '@var_1')
AND ([table_name].[col2] LIKE '@var_2')
AND (' + @column + ' is null OR ' + @column + ' = '''' OR ' + @column + ' = ''N/A'' OR ' + @column + ' = ''Unknown'')' /*I want to use same column name here as well*/

  exec (@SQL)

  When I run this query I get error:
  An error occurred while executing the query.
  Incorrect syntax near '@var_1'. (Microsoft SQL Server Report Builder)

  I can't find out why am I getting this error as the error itself is not very informative.
  Any clues on what might be the reason of this error would be very helpful.

• SqlServer300

  Mr or Mrs. 500

  Points: 536

  More actions

  May 11, 2017 at 1:57 pm

  #1942228

  Got answer on SO. Had to change query as:
  WHERE
([table_name].[col1] LIKE ''%' + @var_1 + '%'')
AND ([table_name].[col2] LIKE ''%' + @var_2 + '%'')