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

sheldonhull June 20, 2017 16:56

Comments

4 comments

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 June 23, 2017 08:59

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

sheldonhull July 13, 2017 02:24

0
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
FredericoJ July 18, 2017 15:56

0
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 August 23, 2017 13:44

0

Add comment

Please sign in to leave a comment.

How can we help you today?

Formatting Error With Output Clause

Comments

Add comment