Dynamic select statement

Question

We have a table in the database and this table has a column called 'fieldName'. In fieldName is a name of a column in another table. Basically this is part of a UI where a user can add a question to a form and when that happens we store that information in fieldName and then create a custom table with a column name that will match one field name.

What I want to do is generate a dynamic SQL statement to get all the fieldNames and build that into a SQL statement. So far I have this:

DECLARE @CFTable INT
DECLARE @columnNames VARCHAR(8000) 

SET @CFTable = 693


SELECT @columnNames = COALESCE(@columnNames + ', ', '') + CONVERT(VARCHAR(25),fieldName )
FROM [FSM_CustomFormColumn]
WHERE CustomFormID = @CFTable

print @columnNames

The result of the above is the column names, like this:

HearAboutEvent, ParticipatedBefore, WeatherDependent, NonRefundable, TransferFee

So if I have all those column names stored in the @columnNames variable, from here I want to do something like this:

select @columnNames from table

This is what I don't know how to get working and need help with.

Solomon Rutzky · Accepted Answer · 2014-07-03 17:44:02Z

1

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT ' + @ColumnNames + N' FROM TABLE;'

EXEC(@SQL);

In your original SQL, change @ColumnNames to be defined as NVARCHAR(MAX). Internally all object names, etc have a type of SYSNAME which equates to NVARCHAR(128).
You also should not need the CONVERT(VARCHAR(25), fieldName), just use fieldName by itself.

answered Jul 3, 2014 at 17:44

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Louis · Accepted Answer · 2014-07-03 17:52:50Z

0

you would have to do this using dynamic sql. Assuming MSSQL, you dynamic sql would be:

`declare @sql varchar(200), @columns varchar(1)='*'

set @sql = 'select ' + @columns + ' from <>'

exec(@sql)`

The takeaway being: You can execute a string as sql using the exec(...) function

answered Jul 3, 2014 at 17:52

Louis

1,2411 gold badge11 silver badges15 bronze badges

Collectives™ on Stack Overflow

Dynamic select statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related