SQL Server Variants not equating

Question

Just had an odd error. I have a function that has 2 variables declared as SQL Variants, being @CCY1 and @CCY2. In the code I simply say something like this...

IF @CCY1=@CCY2
BEGIN
    SELECT @fx_rate=1
    RETURN @fx_rate
END

On my laptop copy of production if @CCY1 is equal to 'AUD' and @CCY2 is also equal to 'AUD' the code in the BEGIN/END executes. However on the production environment, that does not hold and @CCY1 <> @CCY2.

It is an easy fix by casting to a VARCHAR in this instance. My question is why would the behaviour be different on the two different environments (Both as SQL Server 2016)?

Also, check that the Collation is the same between both servers. — Zac Faragher, May 10 '17 at 04:04
@ZacFaragher != is just a different way of writing <> for sql server. Read about it [here](http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql) — t-clausen.dk, May 10 '17 at 05:20
Collation is different - Latin1_General_CS_AI v's Latin1_General_CS_AS. Could explain why the different results. Begs the question - why on the sever with Latin1_General_CS_AI, 'AUD' <> 'AUD' when the variable type is variant. — Ben Watson, May 10 '17 at 05:20
Where do the values assigned to the variables come from? A table? Hard coded into the script? — Martin Smith, May 10 '17 at 07:09

score 1 · Answer 1 · answered May 10 '17 at 06:37

In order to check for differences in the sql_vairiant fields, you can use SQL_VARIANT_PROPERTY.

Can you check if the collation of the first variable is different from the collation of the second?

DECLARE @A1 SQL_VARIANT = 'AUD' COLLATE Latin1_General_CS_AI
       ,@A2 SQL_VARIANT = 'AUD' COLLATE Latin1_General_CS_AS;

SELECT SQL_VARIANT_PROPERTY (@A1, 'Collation');  
SELECT SQL_VARIANT_PROPERTY (@A2, 'Collation');

SELECT IIF(@A1 = @A2, 1, 0); -- not true

SET @A1 = CAST(@A1 AS VARCHAR(24));
SET @A2 = CAST(@A2 AS VARCHAR(24));

SELECT SQL_VARIANT_PROPERTY (@A1, 'Collation');  
SELECT SQL_VARIANT_PROPERTY (@A2, 'Collation');

SELECT IIF(@A1 = @A2, 1, 0); -- true

As you can see, when you are performing cast the default database collation is used. You can check some of the other properties if the collation of the two variables is the same.

SQL Server Variants not equating

1 Answers1