Populate hashbytes column with 0 when source column is NULL or 0

I am trying to insert a value of 0 in a hash bytes column which is of data type Binary(32). Basically, a column in the source can have a value or NULL or 0, I would like to insert a value of 0 (0x0000000000000000000000000000000000000000000000000000000000000000) if the source column value is NULL or 0 if not insert the value.

declare @colA INTEGER,
        @colB INTEGER 
set @colA = 12345  
set @colB = 11111

select convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), src.colA)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) as hashkey_test 

**Result#1**
| hashkey_colA                                                      | hashkey_colB                                                        |
| ----------------------------------------------------------------- | ------------------------------------------------------------------- |
| 0x69B8185C9FF050D693D06C097229183A1BFB5D95FF8A31FFFD4F234076FF0869| 0x540EFEB2DE09E9415CBFF4DF0439E0C8A4898008B9DC55B1FE5A9451E576B149            |


declare @colA INTEGER,
        @colB INTEGER 
set @colA = 12345  
set @colB = 0

select 
case when isnull(@colA,0) = 0 then 0 else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colA)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) 
end as hashkey_colA
,case when isnull(@colB,0) = 0 then 0 else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colB)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) end as hashkey_colB

**Result#2:**
| hashkey_colA                                                      | hashkey_colB                                                        |
| ----------------------------------------------------------------- | ------------------------------------------------------------------- |
| 0x0000000000000000000000000000000000000000000000000000000076FF0869| 0x0000000000000000000000000000000000000000000000000000000000000000            |

I tried using a case statement to evaluate the source value but the hash-key value changes when I use the case statement: see the difference between Result#1 and Result#2 for hashkey_colA. Probably this happens because the data type of highest precedence will be considered in the case of TRUE statement vs FALSE statement.

How can I handle this precedence and make sure I am maintaining the same hash bytes value for hashkey_colA for Result#1 and Result#2 while using a case statement?

Solution

In the second SQL query with case statements, instead of giving when isnull(@colA,0) = 0 then 0 , convert the zero into binary(32) type and give the statement as when isnull(@colA,0)=0 then convert(binary(32),0)

code:

declare @colA INTEGER,
        @colB INTEGER 
set @colA = 12345
set @colB = 0
select
case when isnull(@colA,0)=0 then convert(binary(32),0) else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colA)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) end as hashkey_colA,
case when isnull(@colB,0)=0 then convert(binary(32),0) else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colB)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) end as hashkey_colB

Result

hashkey_colA	hashkey_colB
0x69B8185C9FF050D693D06C097229183A1BFB5D95FF8A31FFFD4F234076FF0869	0x0000000000000000000000000000000000000000000000000000000000000000

db<>fiddle