Dynamic SQL

Transact-SQL
1

2018_05_19%20(12_09_17)

Table_Name Field_Name IS_Visible
Table_ABC Field_01 1
Table_ABC Field_02 1
Table_ABC Field_03 0
Table_XYZ Field_01 1
Table_XYZ Field_02 1
Table_XYZ Field_03 0

I have a table that has meta data about other tables. How would I get the following results DYNAMICALLY (with N groups), basing from the provided sample (above picture or above data)?

RESULTS:

SELECT
	  'From Table_ABC'
	, Field_01
	, Field_02
FROM Table_ABC

SELECT
	  'From Table_XYZ'
	, Field_01
	, Field_02
FROM Table_XYZ

Once I get the above, I would like to able to execute that dynamic SQL string with something similar to:

EXECUTE sp_executesql @SQLQuery

Thank You

2

I might be able to get to the result on my own but would appreciate if someone can answer this for me. Both produce the same "row to string" concatenation result but why choose one (FOR XML PATH) over the other?

SET @Fields =''
SET @Fields =
(
SELECT
       CHAR(10) + CHAR(9) + QUOTENAME(aT1.Field_Name) + CHAR(9) + ' AS ' + QUOTENAME(aT1.Field_Name) + ','
FROM #TEMPTable aT1
ORDER BY aT1.My_ID
FOR XML PATH ('')
) 
PRINT @Fields

SET @Fields =''
SELECT
       @Fields = @Fields + CHAR(13) + CHAR(10) + CHAR(9) + QUOTENAME(aT1.Field_Name) + CHAR(9) + ' AS ' + QUOTENAME(aT1.Field_Name) + ','
FROM #TEMPTable aT1
ORDER BY aT1.My_ID
PRINT @Fields
3

I was able to get to the result, based on some code researching, using STUFF, FOR XML PATH and grouping.

However, would still like to understand my previous question on why use FOR XML PATH over the second query.

In case someone else might need this and feel free to critique for me. Thank You

--FOR XML PATH('') is used to concatenate multiple rows of data into a single string.
--STUFF removes the leading comma
SELECT
	  QUOTENAME(aT2.Table_Name)			AS [Table_Name]

	, 'SELECT' + CHAR(13) + CHAR(10) + CHAR(9) +
	--SUBSTRING(
	STUFF(
			(
			SELECT
				CHAR(10) + CHAR(9) + ', ' + QUOTENAME(aT1.Field_Name) + CHAR(9) + ' AS ' + QUOTENAME(aT1.Field_Name)
			FROM #TEMPTable aT1
			WHERE aT1.Table_Name = aT2.Table_Name
			AND aT1.Is_Visible = 1
			ORDER BY aT1.My_ID
			FOR XML PATH ('')
			)
	, 1, 3, ' ')		--For STUFF function (STUFF vs REPLACE) Replace function replaces everything in the string.  Stuff function replace only a section of the string.
	--, 4, 2000)		--For SUBSTRING function
	+ CHAR(13) + CHAR(10) +
	'FROM ' + QUOTENAME(aT2.Table_Name) + CHAR(13) + CHAR(10)
	AS [Dynamic_SQL]
FROM
(
	SELECT Table_Name FROM #TEMPTable
	GROUP BY Table_Name
) aT2
4

I think, you can use a cursor with loop to make a sql string to execute.

5

Phat, yeah, that is what I did.

Would you be able to answer my second post (above), the difference between the two SQLs?

Thanks

6

Results of both are equal, but the second use recursion.

1 Like
7

The written code can able to generate the required output,
But, I would like to add one thing here,

  1. Could have added the schema name before the table name in the [Dynamic_SQL] column which could work without issue if any table has other than default schema