Return to Answer

edited body

edited May 8, 2019 at 23:42

49.2k
3
53
102

 SELECT @dummy = coalesce(CAST(COL1 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL2 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL3 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL4 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL5 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL6 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL7 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL8 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL9 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL10 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL11 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL12 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL13 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL14 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL15 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL16 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL17 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL18 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL19 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL20 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL21 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL22 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL23 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL24 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL25 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL26 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL27 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL28 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL29 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL30 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL31 AS BINARY(4)), 0xFFFFFFFF0x80000000) + coalesce(CAST(COL32 AS BINARY(4)), 0xFFFFFFFF0x80000000) FROM dbo.TABLE_OF_32_INTS

 SELECT @dummy = coalesce(CAST(COL1 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL2 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL3 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL4 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL5 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL6 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL7 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL8 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL9 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL10 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL11 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL12 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL13 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL14 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL15 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL16 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL17 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL18 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL19 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL20 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL21 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL22 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL23 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL24 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL25 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL26 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL27 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL28 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL29 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL30 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL31 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL32 AS BINARY(4)), 0xFFFFFFFF) FROM dbo.TABLE_OF_32_INTS

 SELECT @dummy = coalesce(CAST(COL1 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL2 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL3 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL4 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL5 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL6 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL7 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL8 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL9 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL10 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL11 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL12 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL13 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL14 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL15 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL16 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL17 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL18 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL19 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL20 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL21 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL22 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL23 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL24 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL25 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL26 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL27 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL28 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL29 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL30 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL31 AS BINARY(4)), 0x80000000) + coalesce(CAST(COL32 AS BINARY(4)), 0x80000000) FROM dbo.TABLE_OF_32_INTS

Source Link

answered May 8, 2019 at 22:07

David Browne - Microsoft

49.2k
3
53
102

If you can ensure ahead-of-time that you don't store some particular int, like -2,147,483,648 then you can do something like:

 SELECT @dummy = coalesce(CAST(COL1 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL2 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL3 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL4 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL5 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL6 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL7 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL8 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL9 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL10 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL11 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL12 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL13 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL14 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL15 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL16 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL17 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL18 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL19 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL20 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL21 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL22 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL23 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL24 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL25 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL26 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL27 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL28 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL29 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL30 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL31 AS BINARY(4)), 0xFFFFFFFF) + coalesce(CAST(COL32 AS BINARY(4)), 0xFFFFFFFF) FROM dbo.TABLE_OF_32_INTS