SQL Server added another function CHOOSE(). which returns a value from the list of given values based on a given position. Suppose, below query will give output "Two".

SELECT CHOOSE(2,'One','Two','Three','Four')   

You can specify constants or variables or expressions as arguments. However, we can't give queries as arguments. Below query will returns error.

declare @t table(ID int,GrpID int)
insert into @t values (10,1),(15,1),(20,2),(12,1),(25,2)
select choose((select GrpID from @t where ID = 10),'one','two','three')

Solution is, create a variable and assign the query result to the variable and use that variable in choose() function.

declare @t table(ID int,GrpID int)
insert into @t values (10,1),(15,1),(20,2),(12,1),(25,2)
declare @GrpID int = (select GrpID from @t where ID = 10)
select choose(@GrpID,'one','two','three')

Published under: SQL Server Tips ·  ·  ·  ·