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

Formatting Error With Output Clause

In update clause, you can specify the output with an alias. This is one odd case where apparently the
ALIAS  = VALUE
is not supported. This means the output clause must be designated as
VALUE AS ALIAS
. The new alias format option incorrectly reformats as output ALIAS = VALUE requiring me to bypass formatting this text otherwise you get an error:
Incorrect syntax near '='.

This is an edge case, but I did run into it today! Learned something new.

Here's an example for the prompt team.
Please let me know if better location to post this, as support button open this page up.
if object_id('tempdb..#temptest','U' ) is not null
	drop table #temptest;
create table #temptest(test_column int)


update #temptest
set test_column = 1 
output  ResultDescription = 'Value Being Manually Set--- Will be considered invalid syntax'
	,test_column_deleted = Deleted.test_column
    ,test_column_new = Inserted.test_column
where 1=1 and 'RedGate' = 'Awesome'

-- SQL Prompt formatting off
update #temptest
set test_column = 1 
output   'ALIAS AS - works fine' as ResultDescription 
	, Deleted.test_column as test_column_deleted
    ,Inserted.test_column as test_column_new 
where 1=1 and 'RedGate' = 'Awesome'
-- SQL Prompt formatting on

Comments

  • Options
    Hi Sheldon,

    Thanks for the report, I've reproduced the problem with your submitted code. Here's a minimum example based on yours which exhibits the issue:
    update #temptest
    set test_column = 1
    output test_column_new = Inserted.test_column
    

    Unfortunately the Microsoft Parser which we use for formatting does not support the equals alias syntax:
    https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

    In fact when I run the query on my local database, SSMS returns:
    Msg 102, Level 15, State 1, Line 8
    Incorrect syntax near '='.
    

    Are you able to run this query successfully, what version of SQL Server/SSMS are you using?

    Best wishes,
    Michael
    Michael Clark
    Technical Lead, SQL Monitor
  • Options
    sheldonhullsheldonhull Posts: 35 Silver 1
    The issue is not that I want to have it format that way, but that SQL prompt option for aliasing was incorrectly changing to the unsupported syntax.

    If this has been fixed I will test to confirm I can not reproduce the issue again.
  • Options
    Hi @sheldonhull,

    This has not yet been fixed.

    If you have Options > Styles > Apply column alias style > Alias Style: alias = column, then it will apply the refactoring regardless of the affected T-SQL clause (i.e. including UPDATE).

    In other words valid SQL:
    update #temptest
    set test_column = 1
    output Inserted.test_column AS test_column_new
    

    Gets transformed to invalid SQL:
    update #temptest
    set #temptest.test_column = 1
    output test_column_new = Inserted.test_column
    

    I've created an internal bug ticket (reference=SP-6454) to get this fixed.

    Kind regards,

    Frederico
    Software Engineer
    Redgate Software
  • Options
    Hi @sheldonhull,

    This issue has been fixed in our latest release. We no longer convert to the alternate alias style when there is an OUTPUT clause.

    You can download it here:
    http://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_8.0.9.2226.exe

    Thanks again for reporting.

    Best,
    Michael
    Michael Clark
    Technical Lead, SQL Monitor
Sign In or Register to comment.