DECLARE @table as NVARCHAR(MAX) = N'[Credit].[Cards]'
 
DECLARE @sql AS NVARCHAR(MAX) = N'SELECT 
    [Card Type]
,   [Card Owner]
,   [Card Number] FROM' + @table + 'WHERE ' + '[Card Type] = ''SuperiorCard'''
    

EXEC sp_executesql @sql;

• When I use the above inside Microsoft Report Builder I get 3 fields created and everything works fine. But I need SuperiorCard to be a parameter.

• If inside SSMS I create a Variable @Customer at the top and do ... '[Card Type] = ''''+@Card+''''... it works fine and it choose card type based on that variable.

• So now I delete declared variable and transfer this to Microsoft report Builder and builder creates the @Parameter but can't see the fields.

• I do ... '[Card Type] = '+ CONVERT(NVARCHAR(MAX),@Card)... and it also create parameter but not the fields and I also can't manually connect the fields. The fields are only created when [Card Type] is hardcoded in a string.

I know this looks like using DYNAMIC SQL is not needed but this is just an example. If I get this I can apply it to my project.

Could you Help please? I need to know how to make this work. I am new to SQL and bad at SQL terminology yet - would be very great if you can explain using human words, how should this code look like in order this to work?

Many thanks in advance

Inside builder, I did try to put the code with hardcoded string so I get the fields and then I change the code to the one that creates the parameter but this don't work. It creates parameter and remove fields.