Columns do not have data type when created with CASE

gerthauan

@Jon_Kirkwood Hi. I was a bit quick to accept your answer as the solution for the previous question regarding data type not present in temp-tables when created with a CAST. Since I did not hear anything on my question to that old post, I will create a new one here.

 Allthough the problem with the cast-columns has been fixed (in 10.13.13.2147), there is still a problem if the column was created with a CASE-statement.

If I use the same example again:

DROP TABLE IF EXISTS #TempTableName;

SELECT  CAST(bffs.ID AS varchar(10))                                AS ID

      , bffs.ServerName

      , bffs.DatabaseID

      , CAST(bffs.DatabaseID AS varchar(6))                         AS DatabaseIDConverted

      , bffs.TypeDesc

      , CASE WHEN bffs.TypeDesc = 'ROWS' THEN 'ROWS' ELSE 'LOG' END AS TypeDescWithCast

INTO    #TempTableName

FROM    dbo.BlitzFirst_FileStats bffs;

SELECT  * FROM  #TempTableName ttn;

If I hover over the #TempTableName and collect the create table script that SQLPrompt is suggestion, it turns out like this:

CREATE TABLE [#TempTableName]

(

[ID] VARCHAR(10),

[ServerName] NVARCHAR(128),

[DatabaseID] INT NOT NULL,

[DatabaseIDConverted] VARCHAR(6),

[TypeDesc] NVARCHAR(60),

[TypeDescWithCast]

)

As you can see, the column created with CASE-expression, the one named TypeDescWithCast, has no data type in the create table-script.

Could you pass this along to the developers?

Thx

Jon_Kirkwood

Hi @gerthauan

 

Thank you for creating the new post, as it does seem the previous one had archived once a solution was found. Bringing your issue back into the spotlight, so we can work on it.

 

I've generated a similar case and replicated the results you have shown on the current version v10.13.15. This has been escalated to our development team for their input, and I shall update this post with their findings and/or resolution.

 

Jon_Kirkwood

Hi @gerthauan

I can announce that SQL Prompt release 10.13.16 includes a fix for this issue.

 10.13.16.3650 - Released on 26 October 2023

Fixes

• Fixed an issue where tabs would not restore when connection was of Active Directory Password type.
• Fixed connecting to Azure via Azure Active Directory Password in SSMS 18.
• Fixed an issue where comments would not align correctly.
• Fixed an issue where column data type was missing in temp table created from SELECT with CASE.

 Testing this I can see that a data type is being defined in my temp table

 

 

 Download link for this version can be found here:

https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.13.16.3650.exe