thanks both for replies, most helpful; will definitely hardcode the numbers here, much better;

thanks to Ed B as well for identifying where the problem was:

my NUMBERS table is/was a Memory-optimised...

tvf pasted below;

the reason is exactly as you suggest...the CTE is evaluating POWER(2, Number) for all numbers

from the numbers table, i.e. up to 4096, hence the overflow, even though only...

Sorry, I should have been more specific; the reason I don’t use a temp table is the CTE is in an inline TVF which takes @status as a...

I thought I did....i can see it anyway

declare @status as int=83457678;

;with nums(num, 2power_Num, bitcheck)

as

(

SELECT Num, POWER(2, Num), (@status & POWER(2,Num))

FROM dbo.Numbers WHERE Num<30

---get only numbers 0-29

)

SELECT MAX(Bitcheck) FROM...