Geek asks and answers

Dynamically accessing SQL Server columns in SP

SQL 2008

Hello,

I have a completely different task that I have to do in SQL. This is a little more than that, but I will try to make it simple.

I need to somehow SELECT a column dynamically. Like this:

declare @ColName varchar(50)

select @ColName = 'Column1' --This is an actual column name in a real table called 'MyTable'

select @ColName from MyTable where Column2 = 123

Is there a way to do something like this? Any help or guidance would be greatly appreciated!

Thanks Jason

+3
source share
4 answers

Read the link in @SQLMenace's answer!

declare @ColName varchar(50)
select @ColName = 'Column1'

declare @sql varchar(MAX)
select @sql = 'select ' + @ColName + ' MyTable where Column2 = 123'

exec (@sql)
0
source

you need dynamic SQL, but first read The Curse and Blessings of Dynamic SQL to make sure you're not opening yourself up toSQL Injection

+1
source 
DECLARE @colNameIn AS varchar(50) = 'Column1'

DECLARE @template AS varchar(MAX) = 'select {@ColName} from MyTable where Column2 = 123' -- This template can be expanded

-- Protect yourself from injection or invalid columns:
DECLARE @ColName AS varchar(50)
SELECT @ColName = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' 
    AND COLUMN_NAME = @ColNameIn

IF @ColName IS NOT NULL
BEGIN
    DECLARE @sql AS varchar(MAX)
    SET @sql = REPLACE(@template, '{@ColName}', QUOTENAME(@ColName))
    EXEC (@sql)
END
+1

, CASE, .

DECLARE @ColName varchar(50)

SET @ColName = 'Column1'

SELECT CASE @ColName
         WHEN 'Column1' THEN Column1
         WHEN 'Column2' THEN Column2
       END
FROM MyTable
WHERE Column2 = 123
0

More articles:


All Articles