Jordan Davis Jordan Davis - 6 months ago 25
SQL Question

table column as variable

I'm getting a syntax error in my stored procedure when trying to use a variable as a reference to a tables column.

BEGIN
SET @mycolumn = (SOME SELECT STATEMENT RETURNING MY COLUMN);
SELECT a.@mycolumn FROM mytable as a;
END


Question: What is wrong with my syntax?

Answer

It looks like you're trying to do dynamic SQL. Here is one way to do it:

BEGIN
SET @mycolumn = (SOME SELECT STATEMENT RETURNING MY COLUMN);
DECLARE @sql nvarchar(max) = 'SELECT a.' + @mycolumn + ' FROM mytable as a';
EXEC sp_executesql @sql
END