humudu humudu - 3 days ago 5
SQL Question

MSSQL hashbytes convertion inconsistency?

when I execute this hardcoded, i get the correct result

Declare @result nvarchar(32)

Set @result = convert(varchar(32), hashbytes('MD5', '1' + 'One' + 'Two' + 'Three'), 2)

select @result


result: 4173AB4C6EE66BC1FF7B7E5D44A872CA (correct)

But when I call/execute this stored procedure, giving it the same parameters, its a different result.

ALTER Procedure [db_owner].[CheckTheTransaction]
@DataID nvarchar(50),
@Data1 nvarchar(50),
@Data2 nvarchar(50),
@Data3 nvarchar(50)
as
Declare @result nvarchar(32)
Set @result = convert(varchar(32), hashbytes('MD5', @DataID + @Data1 + @Data2 + @Data3), 2)

Select @result


my execution:

DECLARE @result int

EXEC @result = [db_owner].[CheckTheTransaction]
@DataID = '1',
@Data1 = 'One',
@Data2 = 'Two',
@Data3 = 'Three'

SELECT 'Result' = @result

GO


result: 5BD42777932EE959AD5A4C9FEE142F00 (wrong)

What can be wrong?

Answer

Change all nvarchar datatype as varchar

ALTER Procedure [db_owner].[CheckTheTransaction]
@DataID varchar(50),
@Data1 varchar(50),
@Data2 varchar(50),
@Data3 varchar(50)
as
  Declare @result nvarchar(32)
  Set @result = convert(varchar(32), hashbytes('MD5', @DataID + @Data1 +         
  @Data2 + @Data3), 2)

Select @result
Comments