CAST columnname with square brackets and no space after AS

atolmeijer

Hi,

I found an error in SQL Server Prompt when CAST is used with a column name in square brackets and no space between the final bracket and the AS keyword. Though the query is valid according to SQL Server's DBE, SQL Prompt fails when the brackets are removed.

Query:

SELECT Name

      ,SUM(CAST([NumberOfProducts]AS bigint))                AS NumberOfProducts
      ,SUM(CAST([NumberOfItems]AS bigint))                   AS NumberOfItems
FROM
   ( VALUES ( 'Item1', 12, 22 )
          , ( 'Item2', 14, 25 ) ) AS orders( Name, NumberOfProducts, NumberOfItems )
GROUP BY Name

Result:

SELECT orders.Name
      ,SUM(CAST(orders.NumberOfProductsAS bigint))                AS NumberOfProducts
      ,SUM(CAST(orders.NumberOfItemsAS bigint))                   AS NumberOfItems
FROM
   ( VALUES ( 'Item1', 12, 22 )
          , ( 'Item2', 14, 25 ) ) AS orders( Name, NumberOfProducts, NumberOfItems )
GROUP BY orders.Name

Error: 'Incorrect syntax near bigint'

Will this be fixed?

Regards,

Arno

Russell D

Ok different day a different result, I've rebooted since so perhaps my ssms was just playing up.

So now it works for me after formatting, without the brackets:

Russell D

This doesn't execute as valid syntax, without Prompt installed, in SQL 2017. 

Are you sure this is a Prompt problem? Where are you seeing this as valid SQL?

atolmeijer

@version 'Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - 12.0.5214.6 (X64)   Jan  9 2018 15:03:12   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) ', SSMS v17.8.1: