Return a single column that matches a parameter

Question

In a stored procedure scenario, I would like to return a single column that matches a specific condition while assigning that column the appropriate name.

• The problem with the following technique is that it returns all columns including those that do not match the condition and assign null values to them.

  SELECT DISTINCT
  CASE WHEN @ColumnName = 'A' THEN s.suborderid END AS ColA
  , CASE WHEN @ColumnName = 'B' THEN s.orderid END AS ColB
  , CASE WHEN @ColumnName = 'C' THEN s.envelopeid END AS ColC
  

How to return only the first column that matches the @ColumnName parameter in this case?

Updated with a Demo

The goal is to return the ReadyDesc column only.

Answer 1

Certainly Dynamic SQL or an extended CASE would be more performant, but the following is Dynamic without actually going Dynamic.

Example

DECLARE @ColumnName nvarchar(50) = 'Ready';

Select Distinct C.*
 From  #ImagesTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Field = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') in (@ColumnName )
             ) C

Returns

Field   Value
Ready   0
Ready   1

Answer 2

You can use a IF .. ELSE IF block in your stored proc

IF @ColumnName = 'Ready'
BEGIN
     SELECT DISTINCT Ready  As ReadyDesc
     FROM #ImagesTable
END
ELSE IF @ColumnName = 'Quality'
BEGIN
     SELECT DISTINCT Quality As QualtiyDesc
     FROM #ImagesTable
END

Edited your Demo

Answer 3

Using Dynamic Query should the return the column you asked for.

create table  #ImagesTable(
Quality nvarchar(50),
Ready nvarchar(50))

-- insert into the temporary table
INSERT INTO #ImagesTable(Quality,Ready)
Values
 (1, 1), 
 (1, 1), 
 (1, 1), 
 (1, 1), 
 (1, 1), 
 (1, 0), 
 (1, 0), 
 (1, 0),
 (1, 0), 
 (1, 0);

DECLARE @ColumnName nvarchar(50) = 'Ready';
declare @mysql nvarchar(max)

select @mysql = N'Select distinct ' + @ColumnName + ' from #ImagesTable';

execute sp_executesql @mysql

drop table #ImagesTable