Bug with Output Clause

JimF

I am running 6.4.1.52 and I have found a couple of bugs when using the Output clause.

Open SSMS and enter the following

Use AdventureWorks
Go

Declare @NewContacts table (ContactID int)

Insert Into Person.Contact
        ( NameStyle,
          Title,
          FirstName,
          MiddleName,
          LastName,
          Suffix,
          EmailAddress,
          EmailPromotion,
          Phone,
          PasswordHash,
          PasswordSalt,
          AdditionalContactInfo,
          rowguid,
          ModifiedDate )
Select c.ContactID,
       c.NameStyle,
       c.Title,
       c.FirstName,
       c.MiddleName,
       c.LastName,
       c.Suffix,
       c.EmailAddress,
       c.EmailPromotion,
       c.Phone,
       c.PasswordHash,
       c.PasswordSalt,
       c.AdditionalContactInfo,
       c.rowguid,
       c.ModifiedDate
From Person.Contact As c

Sure, the example is nonsense as it is just adding it's own rows back to itself... But it shows the problem, which is the Output clause.
Now add the Output clause before the Select portion.

Output Inserted.ContactID Into @NewContacts

Let SQL Prompt auto-complete after typing @NewContacts and you now see the following:

Output Inserted.ContactID Into @NewContacts
		          ( ContactID )
		  Values  ( 0  -- ContactID - int
		            )

The Values clause should have never been added. Furthermore the field matching that was added in 6.4 between the columns in the Insert and the Values/Select is now gone. If I remove the output's column list, which is legal, the field matching comes back.

Thanks for looking into this.
Jim

Aaron L

Hi Jim,

Thanks for letting us know about this! I can recreate it here and will look into a fix for you. Would you prefer for the column list to be auto generated here or would you rather it just completed the table name?

Oddly enough it looks like both you and Bob have both hit the same bug in our parser with a column list on an output clause on the same day (which has been in there for quite a while!) - so we've got a fix for the field matching which I'll include in the next build.

Thanks,
Aaron.

JimF

I would prefer the column list. While optional, I think it will be needed more than not.

Thanks,
Jim

Aaron L

I think an option would fit in perfectly with the other code insertion options currently available. I'll see what I can do and I'll hopefully have a new build for you to try tomorrow.

Thanks,
Aaron.

Aaron L

Hi Jim,

I've got a new private build for you here. This should fix the two issues you mentioned in this thread and it's got the option to insert columns on the OUTPUT/INTO clause under Inserted code->Objects & statements.

It also contains the camel case override setting that was in the previous private build I sent to you and as a bonus F2 should work on cursors now

Let us know how you get on.

Thanks,
Aaron.

JimF

Thanks! I am installing it right now!

OK, I have a stupid question related to just your forum software, phpBB that I can't seem to figure out. Why do I see your name in Red all of the time and David's in Green all of the time? I have noticed this with others that use phpBB.

Aaron L

A red name means administrator and green means global moderator (on the front page of the forum there's a tiny "Legend:" at the bottom by the signed in users)