fauxpas fauxpas - 1 year ago 103
SQL Question

How to insert variable types for table headers

if object_id('tempdb..#mysql_cte1') is not null drop table #mysql_cte1
create table #mysql_cte1(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+','+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float)


Hi I have the above, which I would like the header to be named as:

DateTime|Coke TickerID |Coal TickerID |Coke Bid |Coke Ask |Coal Bid |Coal Ask

But when I used the above code, I will get the error:

Incorrect syntax near '+quotename(Concat(@product, '.

Answer Source

Here is the query: I have fixed couple of things:

DECLARE @product varchar(100) = 'COKE'
DECLARE @product2 varchar(100) = 'Coal'
DECLARE @mysql varchar(max)
set @mysql = 'if object_id(''tempdb..#mysql_cte1'') is not null drop table #mysql_cte1 
          create table #mysql_cte1(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+' float,'+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float) 
          SELECT * FROM #mysql_cte1'
           exec(@mysql)

Please note that for a column with name "Ask" I have added float as the type, you change the above query as per your needs

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download