CODEWITHSUNDEEP
sqlsql-server
user3770963
user3770963

Reputation: 47

Selecting column from table when name is stored in a local var

I have this scenario:

declare @data table (Name varchar(100), Surname varchar(100), Extension int )

insert into @data 
    select 
        Comp.Name, Comp.Surname, Comp.Ext 
    from 
        Comp 
    where 
        Comp.Dept = 25

declare @fieldName varchar(100) = 'Name'
declare @getter varchar(100)
select @getter = (@fieldName) from @data where Extension=101

and I need to have in @getter the value of "Jhon" or "Mary" or "Peter" or whatever...

And if I'd change the value of @fieldName to Surname I should get their surnames.

Kind of difficult to explain but what I need is to reference a column name in a query that is stored in a local var

Upvotes: 0

Views: 54

Answers (3)

Mark Schultheiss
Mark Schultheiss

Reputation: 34196

Given your WHERE clause and temp table you might simply select from the table instead, avoiding that temp table. This example uses a CASE, modify/add to it if you need more columns; use other parameters if you need for the Dept and Ext, no tricky code for that needed.

Use TOP 1 if you only need one if there are multiple matches.

DECLARE @col VARCHAR(50)
SET @col = 'Name'
SELECT 
 CASE 
   WHEN (@col='Name') THEN Comp.Name
   WHEN (@col='Surname') THEN Comp.Surname
 END AS SearchName
FROM Comp 
WHERE Comp.Dept=25 
  AND Comp.Ext=101

Note that this does not use dynamic SQL and can be set as a parameterized query making it more secure.

Here is a simple fiddle to test this out: http://sqlfiddle.com/#!3/78920/2/0

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

use the below query.

DECLARE @fieldName VARCHAR(100) = 'Name'
DECLARE @getter VARCHAR(100)=''
DECLARE @sql  NVARCHAR(MAX) ='declare @data table (Name varchar(100), Surname varchar(100), Extension int )
                              insert into @data select Comp.Name, Comp.Surname, Comp.Ext from Comp where Comp.Dept=25
                              SELECT @Output='+@fieldName+' from @data where Extension=101'
EXEC sp_executesql  @sql,N'@Output NVARCHAR(100) OUT',@getter OUT
SELECT @getter

Upvotes: 2

Paul Goodier
Paul Goodier

Reputation: 43

For example:

declare @data table (Name varchar(100), Surname varchar(100), Extension int )
insert into @data select Comp.Name, Comp.Surname, Comp.Ext from Comp where Comp.Dept=25

declare @fieldName varchar(100) = 'Name'
declare @SQL varchar(100) = 'select ' + @fieldname + ' from @data where Extension=101'
execute(@SQL)

Hope that helps

Upvotes: 0

Related Questions