Cannot concatenate parameter to the sql string

[ABhandari]

Hi,

I am facing this wierd problem while creating a simple report in reporting services. The report is to be created for a CRM application. I have created some parameters for the report and I am trying to concatenate it in my sql string. But when I try to execute or refresh the query, it gives me this error:

The datatypes varchar and sql_variant are incompatible in the add operator.

I have spent a hell lot of time looking for a solution but in vain. Here is a sample code that gives the error:

DECLARE @sql as nVarchar(4000)
Declare @HGroupByName1 as nVarchar(100)

Set @HGroupByName1 = @HGroup1 + 'name' --- @HGroup1 is a report parameter


Set @sql = 'Select Cast(' + @HGroup1 + ' as nVarchar(100)) as HGroup1, ' + @HGroupByName1 + ' as HGroupByName1 from dbo.tablename' Exec(@sql)

When I remove the concatenations, I dont receive the error stated. Whats the problem?????

[DanielaTm]

I tried your code like this:

DECLARE @sql as nVarchar(4000)
Declare @HGroupByName1 as nVarchar(100)
Declare @HGroup1 as nVarchar(100)
Set @HGroupByName1 = @HGroup1 + 'name' --- @HGroup1 is a report parameter
print 'sql='+ @sql
Set @sql = 'Select Cast(' + @HGroup1 + ' as nVarchar(100)) as HGroup1, ' + @HGroupByName1 + ' as HGroupByName1 from dbo.tablename' Exec(@sql)
print 'sql='+ isnull(@sql,'')

no error reported, except sql string was null. So, because I used in my exemple Declare @HGroup1 as nVarchar(100), I suggest to use CAST or CONVERT function to convert this parameter. This, of course , does not affect the rest of your code