title: SQL Server: get column value from string column name, assign value to variable
body: <p>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). </p>
  
  <p>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 <code>entity_client</code>, it could be anything at all, though (due to interrogation of <code>INFORMATION SCHEMA</code>) we will have the value assigned to the variable <code>@entity_column_name</code>.</p>
  
  <p>Example preparation SQL:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>I have tried the following:</p>
  
  <pre><code>SELECT TOP 1 @entity_column_name = [@entity_column_value] 
  FROM #foo;
  </code></pre>
  
  <p>...which generates an error </p>
  
  <blockquote>
    <p>Invalid column name '@entity_column_value'</p>
  </blockquote>
  
  <p>I have also tried:</p>
  
  <pre><code>EXEC('SELECT TOP 1 @entity_column_value = [' + @entity_column_name + '] FROM #foo;');
  </code></pre>
  
  <p>which generates another error </p>
  
  <blockquote>
    <p>Must declare the scalar variable "@entity_column_value"</p>
  </blockquote>
  
  <p>The following works, but unfortunately the column name is hard-coded - I wanted to be able to vary the column name:</p>
  
  <pre><code>SELECT TOP 1 @entity_column_value = [entity_client] 
  FROM #foo;
  </code></pre>
  
  <p>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 <code>SELECT</code> output is simply dumped to screen:</p>
  
  <p><a href="https://stackoverflow.com/questions/27522762/get-column-value-from-string-column-name-sql">Get column value from string column name sql</a></p>
  
  <p><a href="https://stackoverflow.com/questions/17210355/get-column-value-from-dynamically-achieved-column-name">Get column value from dynamically achieved column name</a></p>