[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

Adding a field name to dynamic SQL

In the code below, I have a dynamic SQL statement that works (thanks to imitchie). The result set returns a select statement using  

AS ''Promo' + Cast(PromoID as Varchar)

I need to change that to dynamically read the PromoName field that's in the KScoop_Promo table (aka, change Promo1 to Birthday Club, Promo2 to whatever, based on the PromoName in the table).

Help is greatly appreciated.
declare @dsql varchar(max)
declare @SELECT varchar(max)
declare @LEFTJOINS varchar(max)
 
DECLARE	@ActiveCode varchar(2)
DECLARE	@ActiveCode2 varchar(2)
DECLARE	@StoreActive varchar(2)
 
SET @ActiveCode = 1
SET	@ActiveCode2 = 0
 
 
	SELECT  @SELECT =
			coalesce(@SELECT + ',', '') +  -- first one does not need comma
			' ISNULL(Promo' + Cast(PromoID as Varchar) + '.PromoFlag, 0) AS ''Promo' +
			Cast(PromoID as Varchar) + '''', 
			@LEFTJOINS = coalesce(@LEFTJOINS, '') +
			' LEFT JOIN (
					SELECT  pfl.StoreID, pfl.PromoFlag
					FROM    KScoop_PromoFlag pfl INNER JOIN
									KScoop_Promo pro ON pfl.PromoID = pro.PromoID
					WHERE   pro.PromoID = ' + Cast(PromoID as Varchar) + ' ) Promo' + Cast(PromoID as Varchar) + 
				' ON sto.StoreID = Promo' + Cast(PromoID as Varchar) + '.StoreID
	' ---<<--- leave this on the new line!
	FROM KScoop_Promo Order by PromoID
	 
	SELECT @dsql =
	'SELECT  sto.StoreID, sto.StoreName, sto.StoreActive,' + @SELECT +
	'
	FROM KScoop_Store sto ' +
	@LEFTJOINS +
	'
	WHERE StoreActive IN (' + @ActiveCode + ',' + @ActiveCode2 + ')' + 
	'
	ORDER BY sto.StoreName'
 
	EXEC (@dsql)
 
 
Current result set:
StoreID	StoreName	StoreActive	Promo1	Promo2	Promo3	Promo5
19	Axxon	                  1	1	0	1	1
...

Open in new window

0
barnesco
Asked:
barnesco
1 Solution
 
derobyCommented:
Replace the  

'Promo' + Cast(PromoID as Varchar)

with the field you want (between Quotes for 'safety')


declare @dsql varchar(max)
declare @SELECT varchar(max)
declare @LEFTJOINS varchar(max)
 
DECLARE	@ActiveCode varchar(2)
DECLARE	@ActiveCode2 varchar(2)
DECLARE	@StoreActive varchar(2)
 
SET @ActiveCode = 1
SET	@ActiveCode2 = 0
 
 
SELECT  @SELECT =
			coalesce(@SELECT + ',', '') +  -- first one does not need comma
			' ISNULL(Promo' + Cast(PromoID as Varchar) + '.PromoFlag, 0) AS ''' + PromoName + '''', 
			@LEFTJOINS = coalesce(@LEFTJOINS, '') +
			' LEFT JOIN (
					SELECT  pfl.StoreID, pfl.PromoFlag
					FROM    KScoop_PromoFlag pfl INNER JOIN
									KScoop_Promo pro ON pfl.PromoID = pro.PromoID
					WHERE   pro.PromoID = ' + Cast(PromoID as Varchar) + ' ) Promo' + Cast(PromoID as Varchar) + 
				' ON sto.StoreID = Promo' + Cast(PromoID as Varchar) + '.StoreID
	' ---<<--- leave this on the new line!
	FROM KScoop_Promo 
    Order by PromoID
	 
	SELECT @dsql =
	'SELECT  sto.StoreID, sto.StoreName, sto.StoreActive,' + @SELECT +
	'
	FROM KScoop_Store sto ' +
	@LEFTJOINS +
	'
	WHERE StoreActive IN (' + @ActiveCode + ',' + @ActiveCode2 + ')' + 
	'
	ORDER BY sto.StoreName'
 
	EXEC (@dsql)

Open in new window

0
 
barnescoAuthor Commented:
Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now