How to display data of dynamically selected column

Question

I have a table with 20 columns. I am dynamically selecting a column according to the user input and then want to display the selected column with it's data. Here is the code I wrote but the issue is I am getting column name as the data for the selected column. Could anyone please let me know what I am doing wrong here.

ALTER PROCEDURE Getreport
@Subject [varchar](10),
WITH EXECUTE AS CALLER
AS
--Query to generate report

SELECT FirstName, LastName, @Subject FROM Student

You need [prepared statements](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) — user2989408, Jan 30 '14 at 16:51
@user2989408 I am getting this error "Incorrect syntax near 'PREPARE'." — techblog, Jan 30 '14 at 17:03
Actually because its a stored proc, try the solution in the second answer of the previous link. — user2989408, Jan 30 '14 at 17:06

score 1 · Accepted Answer · answered Jan 30 '14 at 17:24

1

You need to use Dynamic SQL:

EXEC('SELECT FirstName, LastName,' + @Subject + ' FROM Student')

answered Jan 30 '14 at 17:24

A.J

382
2
6

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Your procedure should look something like. Similar solution is shown here.

SET @s = CONCAT('SELECT FirstName, LastName, ', @Subject, ' FROM Student');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited May 23 '17 at 11:50

Community

1
1

answered Jan 30 '14 at 17:09

user2989408

3,127
1
17
15

How to display data of dynamically selected column

2 Answers2