Solved

Adding a field name to dynamic SQL

Posted on 2007-12-03
2
802 Views
Last Modified: 2010-04-21
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
Comment
Question by:barnesco
2 Comments
 
LVL 11

Accepted Solution

by:
deroby earned 500 total points
ID: 20396296
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
 

Author Closing Comment

by:barnesco
ID: 31412349
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question