How to get value by dynamic field Name using sql s

Question

I am passing dynamic column name base that column name to get the value and below i my table

Table_CandidateInfo

Id     Name        Age    City 

1      Mazhar      30     Gulbarga

20     Khan        29     Bidar

Example1

Declare @ColumnName varchar(100), @Id int

set @ColumnName='Age' 
set @Id=20

select * from Table_CandidateInfo where ID=@Id and 

I am not able to pass ColumnName with and query because column name is dynamic pass by code. My output should be

29

Example2: If my @ColumnName='City' and @Id=20 then output should be like below

Bidar

Answer 1

I think what you are actually after is the below:

DECLARE @ColumnName sysname, @Id int;
SET @Id = 29;
SET @ColumnName = N'Age';

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT ' + QUOTENAME(@ColumnName) + N' FROM dbo.Table_CandidateInfo WHERE Id = @Id;';
--PRINT @SQL; --Your debugging friend
EXEC sp_executesql @SQL, N'@Id int', @Id = @Id;

Answer 2

Alas, you cannot pass identifiers as parameters. You need to use dynamic SQL:

declare @columnName varchar(100);
declare @Id int;

set @ColumnName = 'Age' ;
set @Id = 20;

declare @sql nvarchar(max);

set @sql = '
select *
from Table_CandidateInfo
where [columnName] = @Id';

select @sql = replace(@sql, '[columnName]', quotename(@columnName));

exec sp_executesql @sql,
                   N'@id int',
                   @id=@id;