I had a requirement to take some HEX strings that get passed in a stored procedure, XOR them and return a string. This ended up being bit trickier than I thought as the casting of HEX strings to VARBINARY is not straightforward. (The opposite is not too difficult thanks to the system UDF varbintohexstr)
Anyway, here is an example that shows the method that I came up with. If someone has a better way, I'd love to hear it.
--example usage:
/*
DECLARE @ResultString VARCHAR(50)
EXEC dbo.up_XOR '0x0000FFFFFFFFFFFF', '0x0000FFFFFF00000', @ResultString OUTPUT
SELECT @ResultString
Result
-------------------------------
0x0000ed30000fffff
*/
CREATE PROC dbo.up_XOR
(@tmp1 VARCHAR(50),
@tmp2 VARCHAR(50),
@ResultString VARCHAR(50) OUTPUT)
AS
DECLARE @Result VARBINARY(50)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParamDefinition NVARCHAR(500)
SET @SQLString = N'SELECT @Result_OUT = CAST(' + @tmp1 + ' AS BIGINT) ^ CAST(' + @tmp2 + ' AS BIGINT)'
SET @ParamDefinition = N'@Result_OUT BIGINT OUTPUT'
EXECUTE sp_executesql @SQLString,@ParamDefinition,@Result_OUT=@Result OUTPUT
SET @ResultString = master.dbo.fn_varbintohexstr(@Result)
posted on Wednesday, May 11, 2005 11:13 AM