INSERT column order in multi-statement TVF

atompkins

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!

Eddie D

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