1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
e_i_pi
  • 4,590
  • 4
  • 27
  • 45

1 Answers1

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;
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Lol, I had *just* found parameterised `sp_executesql` documentation [here](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql). Thank you for the answer, I was struggling with the syntax, it's certainly different to what I'm used to. – e_i_pi Feb 19 '18 at 00:57