Using Dynamic SQL to Specify a Column Name by Adding a Variable to Simple SQL Query

Using dynamic SQL to specify a column name by adding a variable to simple sql query

If you are trying to specify the column name dynamically, you could take a look at executing dynamic sql. However, you should make sure to read about the dangers of this approach first:

http://www.sommarskog.se/dynamic_sql.html

From that page, there is a sample that shows dynamically specifying the table name -- you could change it so it dynamically specifies the column name instead:

CREATE PROCEDURE general_select @tblname nvarchar(128),
                                @key     varchar(10),
                                @debug   bit = 0 AS
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT col1, col2, col3
            FROM dbo.' + quotename(@tblname) + '
            WHERE keycol = @key'
IF @debug = 1 PRINT @sql
EXEC sp_executesql @sql, N'@key varchar(10)', @key = @key

So for example if you had a table 'MyTable' with columns named 'x', 'y', and 'z', it might look like:

DECLARE @columnName nvarchar(128)
DECLARE @sql nvarchar(4000)
set @columnName = 'z'

SET @sql = 'SELECT x, y, ' + @columnName + ' from MyTable'
EXEC sp_executesql @sql, N'@columnName varchar(128)', @columnName = @columnName

SQL Server Query using a Dynamic Column Name

IS <> =, so you need to fix it :

SET @sql = 'select [' + @var1 + '] from [priority matrix] where impact  = ['+ @var2 + ']';

I would use QUOTENAME() instead of manual [] :

SET @sql = 'SELECT '+ QUOTENAME(@var1) +' FROM [priority matrix] WHERE impact  = '''+@var2+'''';

Literal strings are quoted with single quotes, [] are not required.

How can I dynamically reference column names while looping through a query's results?

It's possible using structure notation, but also requires a query row number. The general syntax is

#queryName[ "columnName" ][ rowNum ]# 
<!--- 
.. or specifically 
--->
#getCapabilityAndDescription[ DescriptionVariable ][ 1 ]#

To output all of the query columns dynamically. Use GetMetaData() to retrieve an array of query column properties (in select order). Then use the name property to output each column value:

<!--- Demo query --->
<cfset yourQuery = queryNew("EntryID,Baker3Description"
        , "integer,varchar"
        , [[1,"Descrip A"],[2,"Descrip B"]]
    )>

<cfset meta = getMetaData(yourQuery)>
<cfoutput query="yourQuery">
    <cfloop array="#meta#" index="props">
        #yourQuery[props.name][currentRow]#
    </cfloop>
    <br>
</cfoutput>

Though be careful building that kind of dynamic sql. If any of the values used to build the column names are user supplied (for example getJobDesc.Type) the query will be vulnerable to second order sql injection.

Dynamic SQL get one value from custom @Column into result variable

You can't parametrise an object name, you have to safely inject the value.

This should be what you're after:

DECLARE @QuantityColumnName sysname = N'ColumnName'; --Correct datatype for object names

DECLARE @sql NVARCHAR(MAX);        
DECLARE @qty money;
SET @sql = N'SELECT TOP 1 @qty = ' + QUOTENAME(@QuantityColumnName) + N' FROM #temporaryTable WHERE ID =  @Id;'
EXEC sp_executesql @sql, N' ,@Id bigint, @qty money OUTPUT', @Id = @Id, @qty = @qty OUTPUT;

SQL: Select dynamic column name based on variable

EXEC ('SELECT ''value'' AS ' + @myDynamicColumn)

Dynamically column names from select sql

Is this what you were looking for:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
    from tempData
    group by ColumnName, name
FOR XML PATH(''), Type
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = N'SELECT Name, ' + @cols + N' from 
 (
    select Name, value, ColumnName
    from tempData
) x
pivot 
(
    SUM(value)
    for ColumnName in (' + @cols + N')
) p '

exec sp_executesql @query;

Changing this in your fiddle return the rows as you need it.

---

Related Topics