CODEWITHSUNDEEP
sql-serversql-server-2000
Paul Maidment
Paul Maidment

Reputation: 1024

SQL Server Bug?

I am writing a function to process some CSV data. This is what I have written so far...

CREATE FUNCTION dbo.FGetCommaSeperatedValues(@csv as text)
RETURNS @tblIds TABLE(id int,csvlength int) 
AS
BEGIN
    DECLARE @csvlength AS int
    --SET @csvlength = datalength(@csv);
    SET @csvlength = 7685
    DECLARE @currentIndex AS int
    SET @currentIndex = 0
        WHILE @currentIndex < @csvlength
            BEGIN
                --INSERT INTO @tblIds SELECT @currentIndex,@csvlength
                INSERT INTO @tblIds (id,csvlength) values (@currentIndex,@csvlength)
                SET @currentIndex = @currentIndex+1
            END
    RETURN
END

My issue is that when I execute the function, using the following command...

SELECT * FROM FGetCommaSeperatedValues('')

The table returned does not show the results that I expect.

Everything is fine until around row 3624 or so (as expected, id column increments by 1) Then the values increment erratically.

My colleague tested this code in SQL Server 2008 and everything works fine. This appears to be isolated to SQL server 2000.

Does anyone know of this bug, and/or it's resolution?

Upvotes: 2

Views: 121

Answers (1)

John Sansom
John Sansom

Reputation: 41819

In order to gaurantee sort order in SQL Server you must use the ORDER BY clause.

ORDER BY CLAUSE

Upvotes: 4

Related Questions