CODEWITHSUNDEEP
sql-server
billmac1
billmac1

Reputation: 48

How to include a column in a SELECT query dynamically with a variable?

Using MS Sql Server

I'm executing the following code:

SELECT @Fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
   AND ORDINAL_POSITION = @FIELD

It assigns IsPrimary to @Fieldname, which is the expected answer. But when I execute this line:

SET @MyField =  (SELECT @Fieldname FROM inserted WHERE FileID = @FileID)

@MyField is also assigned the value IsPrimary, instead 1, the value contained in the IsPrimary field.

(Note: Executing SELECT IsPrimary FROM inserted WHERE FileID = @FileID works correctly and returns the expected value of 1)

What's the correct sql statement?

Upvotes: 1

Views: 279

Answers (2)

Kenny Evitt
Kenny Evitt

Reputation: 9801

To elaborate on Crono's answer, and fix my previous answer, the "correct (T-)SQL statement" would be like this:

SELECT FileID = CAST(1 AS int), CreatedAt = GETDATE()
INTO #inserted;

DECLARE @Fieldname  varchar(30),
        @MyField    datetime,
        @FileID     int,
        @sql        nvarchar(4000);

SELECT  @Fieldname = 'CreatedAt',
        @FileID = 1;

SELECT @sql = 'SELECT @MyField = ' + @Fieldname + ' FROM #inserted WHERE FileID = ' + CAST(@FileID AS varchar) + ';';

EXEC sp_executesql @sql, N'@MyField datetime OUTPUT', @MyField OUTPUT;

SELECT [@MyField] = @MyField;

DROP TABLE #inserted;

I added the code to create the temp table #inserted just so you can run the example code as-is.

I'm glad I mentioned Erland's article below, as it states something important about dynamic SQL that explains why my previous answer didn't work:

Within the block of dynamic SQL, you cannot access local variables (including table variables) or parameters of the calling stored procedure. But you can pass parameters – in and out – to a block of dynamic SQL if you use sp_executesql.

See (the classic) The Curse and Blessings of Dynamic SQL by Erland Sommarskog for loads of more info about using dynamic SQL (i.e. generating SQL and then executing it).

Upvotes: 0

Crono
Crono

Reputation: 10478

It doesn't work that way. You cannot use variables to represent portions of your query.

You can, however, build your query string dynamically into a VARCHAR variable and use the EXEC function to execute it. It comes with tradeoffs of all kinds (different security context, risk of sql injection attacks to account for, etc... ), but it will work the way you expect.

Upvotes: 1

Related Questions