usefulBee
usefulBee

Reputation: 9702

Return a single column that matches a parameter

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.

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.

Upvotes: 1

Views: 87

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82020

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

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

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

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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

Upvotes: 1

Related Questions