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

Bug with Output Clause

JimFJimF Posts: 49 Bronze 3
edited October 30, 2014 12:22PM in SQL Prompt
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

Comments

  • Options
    Aaron LAaron L Posts: 596 New member
    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.
  • Options
    JimFJimF Posts: 49 Bronze 3
    I would prefer the column list. While optional, I think it will be needed more than not.

    Thanks,
    Jim
  • Options
    Aaron LAaron L Posts: 596 New member
    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.
  • Options
    Aaron LAaron L Posts: 596 New member
    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.
  • Options
    JimFJimF Posts: 49 Bronze 3
    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.
  • Options
    Aaron LAaron L Posts: 596 New member
    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)
Sign In or Register to comment.