CHOOSE function with out of range index + SQL Server 2012

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 240
If the CHOOSE function's index position is outside of range of values, then it returns NULL...

Suppose, you have passed 4 set of values to CHOOSE function, but specified index position as outside index ( i.e. 5) or any negative index like -1 or NULL. In this case the CHOOSE returns NULL as output

DECLARE @Index INT = 2 -- 1= dev; 2 = Test; 3= QA; 4= Prod
,@DevPath VARCHAR(1000) = 'http://www.dev_genus.com'
,@TestPath VARCHAR(1000) = 'http://www.Test_genus.com'
,@QAPath VARCHAR(1000) = 'http://www.QA_genus.com'
,@PRODPath VARCHAR(1000) = 'http://www.PROD_genus.com'
SELECT CHOOSE( 3, @DevPath, @TestPath, @QAPath, @PRODPath ) as TheRealDeal
,CHOOSE( 5, @DevPath, @TestPath, @QAPath, @PRODPath ) as OutSideRange
,CHOOSE( -1, @DevPath, @TestPath, @QAPath, @PRODPath ) as NegativeIndex
,CHOOSE( 0, @DevPath, @TestPath, @QAPath, @PRODPath ) as ZeroIndex
,CHOOSE( NULL, @DevPath, @TestPath, @QAPath, @PRODPath ) as ZeroIndex


output :
TheRealDeal           OutSideRange	NegativeIndex	ZeroIndex	ZeroIndex
http://www.QA_genus.com NULL NULL NULL NULL

Comments or Responses

Login to post response