INSERT column order in multi-statement TVF

atompkinsatompkins Posts: 22 Bronze 2
edited March 14, 2012 3:59PM in SQL Prompt Previous Versions
SQL Prompt version 5.2.8.2
SSMS 2005 9.00.4035.00

Trying to write a multi-statement TVF and noticed that the INSERT inserter is getting the columns mixed up. This does not happen in ad-hoc SQL or SPs AFAIK.
CREATE FUNCTION dbo.fnMyTest ()
RETURNS @Results TABLE
	(
    id int NOT NULL
   , myString nvarchar(50) COLLATE Latin1_General_CI_AS NOT NULL
   , col1 int NOT NULL
   , col2 int NOT NULL
   , col3 int NOT NULL
   , col4 int NOT NULL
	)
AS 
	BEGIN

		INSERT @Results
				(col1
			   , myString
			   , id
			   , col4
			   , col3
			   , col2
				)
		VALUES	(
				)

		RETURN 
	END

While I'm here, if you format the above code you'll notice that the "NOT NULL" after the collation gets put on a new line. I don't like this, it makes my table definitions messy. Any chance of making it a preference? I'm using a reg ex replace to restore them atm, quite annoying!

Comments

  • Eddie DEddie D Posts: 1,733 Rose Gold 5
    Thank you for your post into the forum.

    Using a test system, I was able to reproduce this error. I have added the details of this forum post to an existing Bug Report, the Bug Report reference is SP-3667.

    I will mark the Bug Report for review. Hopefully the SQL Prompt Development Team will fix this bug in a future version of SQL Prompt.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
Sign In or Register to comment.