Formatting Error With Output Clause

sheldonhull

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

Michael Clark

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

sheldonhull

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.

FredericoJ

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

Michael Clark

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