SQL Server: get column value from string column name, assign value to variable

Question

In SQL Server in a stored procedure, I want to get the value in a column of a single-row table given the column name, and assign that value to a variable. The column name may be different every time (I use T-SQL to interrogate the schema at run time).

The example given below is as minimal as I can make it, the important thing is that you cannot assume that the column name will always be entity_client, it could be anything at all, though (due to interrogation of INFORMATION SCHEMA) we will have the value assigned to the variable @entity_column_name.

Example preparation SQL:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL
BEGIN;
    DROP TABLE #foo;
END;

CREATE TABLE #foo 
(
    id INT,
    entity_client NVARCHAR(255)
);

INSERT INTO #foo VALUES (1, 'clientcode|client_number');

DECLARE @entity_column_name NVARCHAR(255) = 'entity_client';
DECLARE @entity_column_value NVARCHAR(255);

I have tried the following:

SELECT TOP 1 @entity_column_name = [@entity_column_value] 
FROM #foo;

...which generates an error

Invalid column name '@entity_column_value'

I have also tried:

EXEC('SELECT TOP 1 @entity_column_value = [' + @entity_column_name + '] FROM #foo;');

which generates another error

Must declare the scalar variable "@entity_column_value"

The following works, but unfortunately the column name is hard-coded - I wanted to be able to vary the column name:

SELECT TOP 1 @entity_column_value = [entity_client] 
FROM #foo;

Yes, I have looked on SO and found the following questions, but they do not provide an answer where the value is assigned to a variable, in both cases the SELECT output is simply dumped to screen:

Get column value from string column name sql

Get column value from dynamically achieved column name

John Woo · Accepted Answer · 2018-02-19 00:53:28Z

1

This will actually work but you need to declare the output variable:

DECLARE @entity_column_name NVARCHAR(255) = 'entity_client';
DECLARE @entity_column_value NVARCHAR(255);

DECLARE @tsql NVARCHAR(1000) = 'SELECT TOP 1 @entity_column_value = [' + @entity_column_name + '] FROM #foo;'
EXEC sp_executesql @tsql, N'@entity_column_value NVARCHAR(255) OUTPUT', 
                          @entity_column_value OUTPUT;

answered Feb 19, 2018 at 0:53

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

e_i_pi Over a year ago

Lol, I had just found parameterised sp_executesql documentation here. Thank you for the answer, I was struggling with the syntax, it's certainly different to what I'm used to.

Collectives™ on Stack Overflow

SQL Server: get column value from string column name, assign value to variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related