Referencing SQL Server columns dynamically in SP

SQL 2008

Hello,

I have a rather different 开发者_JS百科task I have to do in SQL. It's a bit more involved than this, but I'm going to 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 direction would be greatly appreciated!

Thanks, Jason

you need dynamic SQL, but first read The Curse and Blessings of Dynamic SQL to make sure you don't open yourself up for SQL Injection

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

Read the link in @SQLMenace answer!

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

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

exec (@sql)

You can do this as using a CASE statement if you have predefined all of the acceptable column names in the procedure.

DECLARE @ColName varchar(50)

SET @ColName = 'Column1'

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