What are the challenges you face when working across database platforms? Take the survey

Columns do not have data type when created with CASE

@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, there is still a problem if the column was created with a CASE-statement.

If I use the same example again:

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]
[ServerName] NVARCHAR(128),
[DatabaseID] INT NOT NULL,
[DatabaseIDConverted] VARCHAR(6),
[TypeDesc] NVARCHAR(60),

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?



  • Options

    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 | Technical Support Engineer | Redgate Software
  • Options

    Hi @gerthauan

    I can announce that SQL Prompt release 10.13.16 includes a fix for this issue. - Released on 26 October 2023


    • 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:


    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.