How can we help you today? How can we help you today?

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 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
gerthauan
0

Comments

2 comments

Sort by
  • 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
    0
  • 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

    Jon_Kirkwood
    0

Add comment

Please sign in to leave a comment.