Pass a column name using parameter in select statement

• MaggieW

  SSC Eights!

  Points: 947

  More actions

  September 5, 2014 at 6:26 pm

  #290493

  Dear all:

  I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.

  Here's the table structure, let's call it #temp

  [Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]

  Bike, 200

  Accessory , 100

  I have a table in the DB created as [ProductSales]

  Here's what works for me:

  insert into [ProductSales](Product, Sales)

  Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'

  From #temp

  But in reality the product level is not always on level 03, I want to define a parameter @product and pass it to the select list.

  Here's what I tried to do:

  Declare @product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")

  Declare @SQL varchar(MAX)

  SET @SQL='insert into [ProductSales](Product, Sales)

  Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'

  From #temp'

  Exec @SQL

  But it threw me error, Anyone have any idea what is the right way to do it, is it because the double quote or single quote thing?

  Is it doable this way?

  Any input is welcome!

  Thank you so Much!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 5, 2014 at 6:41 pm

  #1743647

  MaggieW (9/5/2014)

  ---

  Dear all:

  I have a temp table that hosts data from a MDX query, I want to select a column from that table by passing in a parameter and then insert to a new table.

  Here's the table structure, let's call it #temp

  [Product].[Product Hierarchy].[level 03].[Member_CAPTION], [Measures].[Gross Sales]

  Bike, 200

  Accessory , 100

  I have a table in the DB created as [ProductSales]

  Here's what works for me:

  insert into [ProductSales](Product, Sales)

  Select convert(varchar(20), "[Product].[Product Hierarchy].[level 03].[Member_CAPTION]") as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'

  From #temp

  But in reality the product level is not always on level 03, I want to define a parameter @product and pass it to the select list.

  Here's what I tried to do:

  Declare @product varchar(500) = convert(varchar(20), "[Product].[Product Hierarchy].[level 01].[Member_CAPTION]")

  Declare @SQL varchar(MAX)

  SET @SQL='insert into [ProductSales](Product, Sales)

  Select '+@Product+' as 'Product' , convert(decimal(18,2), [Measures].[Data]) as 'Sales'

  From #temp'

  Exec @SQL

  But it threw me error, Anyone have any idea what is the right way to do it, is it because the double quote or single quote thing?

  Is it doable this way?

  Any input is welcome!

  Thank you so Much!

  Try this:

  Declare @product varchar(500) = convert(varchar(20), '[Product].[Product Hierarchy].[level 01].[Member_CAPTION]')

  Declare @SQL varchar(MAX)

  SET @SQL='insert into [ProductSales](Product, Sales)

  Select '+@Product+' as ''Product'' , convert(decimal(18,2), [Measures].[Data]) as ''Sales''

  From #temp'

  Exec (@SQL)