2

I have written a stored procedure that hashes the value of a certain column. I need to use this HASHBYTES function in a CASE WHEN or IIF statement, like this:

DECLARE @Hash varchar(255) = 'testvalue'
SELECT    IIF(1=1, HASHBYTES('SHA1',@Hash), @Hash)
SELECT CASE WHEN 1=1 THEN HASHBYTES('SHA1',@Hash) END  AS Hashcolumn

I can't get my head around why I get different outputs from above queries? it seems that whenever I add an ELSE in the CASE WHEN / IIF statement, it returns a string of weird characters (like ü<þ+OUL'RDOk{­\Ìø in above example).

Can anyone tell me why this is happening? I need to use the CASE WHEN or IIF.

Thanks guys

JVGBI
  • 565
  • 1
  • 8
  • 26

2 Answers2

5

IIF returns the data type with the highest precedence from the types in true_value and false_value. In this case, it's @Hash1 which is varchar(255) so your result is getting cast to varchar(255). See below.

DECLARE @Hash varchar(255) = 'testvalue'
SELECT cast(HASHBYTES('SHA1',@Hash) as varchar(255))

Similarly, CASE works the same way. However, if you don't add an ELSE or another WHEN that would conflict with the data type, it will work. This is because an ELSE NULL is implied. i.e.

SELECT CASE WHEN 1=1 THEN HASHBYTES('SHA1',@Hash) END

However, if you add another check, then precedence kicks in, and it will be converted. 

SELECT CASE WHEN 1=1 THEN HASHBYTES('SHA1',@Hash) WHEN 1=2 THEN @Hash END AS Hashcolumn 
SELECT CASE WHEN 1=1 THEN HASHBYTES('SHA1',@Hash) ELSE @Hash END AS Hashcolumn
S3S
  • 24,809
  • 5
  • 26
  • 45
  • Thanks for the reply! I've posted a new comment, can you take a look at that? I still find it very weird. – JVGBI May 08 '18 at 18:54
  • I've answered your original question, but your comments on your answer below are confusing for me. I'm not sure what the ultimate goal is here and why an `IIF` or `CASE` is needed. I think you should open a new question for your new problem. – S3S May 08 '18 at 19:08
  • The idea is that I hash the data in the column if 1=1 (this is ofcourse another condition, but it doesn't matter), else just show the default record within the column, but I guess this is not possible, as the default record in the column is always varchar and it will be overruled by data precedence if I understand it right. – JVGBI May 08 '18 at 19:21
  • you are correct in your last statement. You can do this one other way, but I'd need to know the real purpose / use case, as another solution may not work for your simple example. – S3S May 08 '18 at 19:39
  • I'm very interested in the other way. Basically what the stored procedure does is the following: 1. Bulk insert a CSV file 2. Take first row based on CRLF and take comma delimited headers 3. Create a temp table from these headers 4. Hash the data in column X (based on inputparameter). If columname from csv = columname, then hash the data in that column. Otherwise just return column values. Does that make any sense? All I really need is a way to bypass the examples, which is the exact problem. I need to either hash or show normal column data(which is always varchar) – JVGBI May 08 '18 at 19:49
  • Unfortunately that’s not enough information. We would need the entire code set. I would accept this answer and open a new one so it gets more attention @ImperialBert – S3S May 09 '18 at 01:38
  • The examples in my main post is exactly what the stored procedure does and where it goes wrong. I just need these examples to work. The entire stored procedure is too long to post here I'm afraid. I just need the CASE WHEN to work WITH an extra WHEN / ELSE statement. Also, the 1=1 is ALWAYS varbinary, and the 1=2 is ALWAYS varchar. Hope you can help me to "bypass" the data precedence in my example. The hash can also be returned as a string btw, it doesn't matter, as the hash will be exported into a CSV file, just to mask the data in that certain column. – JVGBI May 09 '18 at 06:03
  • What I’m saying @ImperialBert is that it’s not enough for me unfortunately and it’s unlikely that this question gets viewed much more so you’d have a better chance of opening a new question. Why are you so against that? This was a good question that got upvoted. – S3S May 09 '18 at 11:19
2

The output of a select query is a virtual table. In a relational db a column of a table is constrained to single data type.. so here what happens is implicit conversion is being done by the server engine inorder to render a sigle type and hence weird characters are returned.

The nature of conversion is as @scsimon says it follows highest precedence order.

The following query should help.

DECLARE @Hash varchar(255) = 'testvalue'
SELECT    IIF(1=1, CONVERT(VARCHAR(255),HASHBYTES('SHA1',@Hash),2), @Hash)
SELECT CASE WHEN 1=2 THEN CONVERT(VARCHAR(255),HASHBYTES('SHA1',@Hash),2) 
      ELSE @Hash END  AS Hashcolumn
Samuel A C
  • 406
  • 3
  • 16