Reputation: 9702
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.
Upvotes: 1
Views: 87
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
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
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