Hashed value not returning as expected in SQL UDF 1

[jimoo]

I created a function to hash a password value. When I run the code without the function it returns the expected value, but the function return other chacter data. I tried using char, varchar, binary, varbinary, and even casting the output but not success.

Here is the function and below are the values hashed manually and by call the value. I expected the same results of up to 40 characters of output per SHA1

USE [NAME_OF_DB_YOU_WANT_FUNCTION_IN]
GO
/****** Object:  UserDefinedFunction [dbo].[HashIt]    Script Date: 07/21/2011 08:29:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[HashIt]
    (
      @TextPW CHAR(40)
    )
--RETURNS BINARY
RETURNS VARCHAR
/* 
Hashes the input text string using SHA1
Result will be evaluated or stored to data in data tables
*/ 
AS BEGIN
    --DECLARE @HashedPw BINARY
    DECLARE @HashedPw CHAR(40)

	SELECT @HashedPw = HashBytes( 'SHA1', @TextPW ) 
    RETURN @HashedPw
 END

Note: you will need to change the following line to your db.
USE [NAME_OF_DB_YOU_WANT_FUNCTION_IN]


To mannually execute and to exeucte function.

SELECT HashBytes( 'SHA1', 'George Washington')
select dbo.HashIt('George Washington')

Results 1
0xE89F0A075045D5AD9965DA19D18DD14D97D86484

Results 2
’

Any ideas how to get results 2 to match results 1





Jim

 

[Qik3Coder]

HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

Arguments
--tra la la --

Return Value
[!]varbinary (maximum 8000 bytes)[/!]

Declare @vb varbinary(100)
select @vb = HashBytes('SHA1', 'GeorgeWashington')
select @vb, convert(varchar(100), @vb)

Store it in the database in a varbinary field.

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD