CODEWITHSUNDEEP
sqlsql-server
Ahmed Nabil
Ahmed Nabil

Reputation: 751

SQL: SELECTing a predefined list of columns

I want to do something like the following statement:

SELECT @column_list FROM table_name

how can I achieve this?

Upvotes: 1

Views: 1740

Answers (3)

Thom A
Thom A

Reputation: 95561

This option takes out the danger of injection by ensuring that the column exists within the table, and also quotes the column's name:

USE Sandbox;
GO

CREATE TABLE Table_name (ID int,
                         SomeString varchar(500));

DECLARE @column_list nvarchar(MAX);
DECLARE @SQL nvarchar(MAX);

SET @column_list = 'ID,Somestring'; --Don't use spaces in the list

SET @SQL = N'SELECT ' +
           STUFF((SELECT N',' + NCHAR(10) + N'       ' + QUOTENAME(SS.value)
                  FROM STRING_SPLIT(@column_list,',') SS
                  WHERE EXISTS (SELECT 1
                                FROM sys.tables t
                                     JOIN sys.columns c ON t.object_id = c.object_id
                                     JOIN sys.schemas s ON t.schema_id = s.schema_id
                                WHERE t.name = N'Table_name' --Change this the applicable table
                                  AND c.name = SS.value
                                  AND s.name = 'dbo') --change this to the applicable schema
                  FOR XML PATH(N'')),1,9,N'') + NCHAR(10) +
           N'FROM Table_name;';
PRINT @SQL;

EXEC sp_executesql @SQL;
GO
DROP TABLE Table_name;

If you aren't on SQL Server 2016+ you can make use of DelimitedSplit8k/DelimitedSplit8K_Lead, or am XML splitter (if you are going to pass a list longer than 4000 characters)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

You would need to use dynamic SQL for that:

DECLARE @sql varchar(500)
DECLARE @column_list varchar(100)
SET @column_list = 'col1, col2, col3'
SET @sql = 'SELECT ' + @column_list + ' FROM table_name';
EXEC (@sql)

Note that @column_list could come from the result of another query, e.g. a query on the Information Schema table to find a certain group of columns.

If you need to also build a query dynamically using the columns from the information schema table, then here is one option:

DECLARE @sql varchar(1000)
DECLARE @column_list varchar(500)
SET @column_list = 'col1, col2, col3'

SELECT @column_list = 
    STUFF((
        SELECT ',' + COLUMN_NAME
        FROM information_schema.columns
        WHERE table_name = 'yourTable'
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @sql = 'SELECT ' + @column_list + ' FROM yourTable';
EXEC (@sql)

To address the fear of SQL injection, I don't consider the above query to be a big risk, because the column names are coming from SQL Server.

Upvotes: 2

Rahul Neekhra
Rahul Neekhra

Reputation: 810

I am not sure what are you looking for but below query might help you. You can try this.

select t.Name AS TableName, c.Name AS ColumnName from sys.tables t
INNER JOIN sys.columns c
ON c.object_id = t.object_id
WHERE t.name  = 'YOUR_TABLE_NAME'

Upvotes: 0

Related Questions