Sql-server – Issue with variable length string between compatibility level 80 and 100

compatibility-levelsql serversql-server-2008sql-server-2016varchar

I was getting tons of issues related with a specific scenario on my current enterprise.

We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to 100, but some of our store procedure are having a problem, demonstrated by the code below:

CREATE TABLE #PRUEBA (texto1 char(30), condicion char(30)) 
INSERT INTO  #PRUEBA VALUES ('PFI','1') 
INSERT INTO #PRUEBA VALUES ('CFI','2') 
SELECT 
    CASE
        WHEN condicion= '1' THEN texto1 
        ELSE 'TFI'
    END 
FROM  #PRUEBA 
DROP TABLE #PRUEBA

Compatibility -> 80 

'PFI                           ' -> char of 30 length 
'TFI                           ' -> char of 30 length

Compatibility -> 90 

'PFI                           ' -> varchar of 30 length
'TFI'                            -> varchar of 3 length

Compatibility -> 100 

'PFI                           ' -> varchar of 30 length
'TFI'                            -> varchar of 3 length

Why did this happen?

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?

Is there any solution to get the same result on compatibility 100 like compatibility 80?

Best Answer

Can we keep the blank spaces to get the same result as compatibility 80, without change the query, just only doing a configuration on the server?

No, not that I am aware of. This is an issue of datatype precedence with regards to the CASE expression. The CASE expression needs to return a consistent datatype no matter which branch is selected. Since VARCHAR has a higher precedence than CHAR, the only choice here is to return VARCHAR. It looks like you will need to update the code to possibly convert the result of the CASE expression to CHAR(30). For example:

SELECT
    CONVERT(CHAR(30), CASE
                        WHEN condicion= '1' THEN texto1
                        ELSE 'TFI'
                      END)
FROM  #PRUEBA

Or, I suppose you can simply convert the string literal to match the datatype of the column:

SELECT
    CASE
      WHEN condicion= '1' THEN texto1
      ELSE CONVERT(CHAR(30), 'TFI')
    END
FROM  #PRUEBA

Also, why are you using compatibility level 80? That is for SQL Server 2000. And you are about to move up to SQL Server 2016? By default that would be compatibility level 130. And lucky for you, level 100 is available in SQL Server 2016, though it is the lowest level available. Still, if you are currently relying upon SQL Server 2000 behavior, you will likely have a lot more to change than just this if moving to SQL Server 2016.

Related Question