My guess is 2 (value, defaultValue) ** 3 (number of rows) = 8

select [@YY],[@XX],[@ID]

from

(select v as [@ID]

from #Param

cross apply(values (value),(DefaultValue) ) t(v)

where Name ='@ID') _1,

(select v as [@XX]

from #Param

cross apply(values (value),(DefaultValue) ) t(v)

where Name ='@XX') _2,

(select v as [@YY]

from #Param

cross apply(values (value),(nullif(DefaultValue,'1')) ) t(v)

where Name ='@YY') _3;