March 21, 2008 at 12:54 pm
Hello.
I have a SELECT statement within a stored procedure that does not contain an ORDER BY clause. Is it possible for SQL Server 2005 to return the rows in different orders for two separate calls with the same parameters and same contents in all related tables?
The parameters for my stored procedure are as follows:
@shapefile nvarchar(max),
@x float,
@y float,
@coids xml
Where the @coids parameter has the following structure (replace the round brackets with angled brackets):
(coids)(coid coid="5678" /)(coid coid="1234" /)...(/coids)
And the stored procedure is this:
BEGIN TRANSACTION
DECLARE @script nvarchar(max)
SET @script = '
SELECT' + @shapefile + '_Cand.oid AS coid, ' + @shapefile + '.oid AS nextcoid, ' + @shapefile + '.ux, ' + @shapefile + '.uy,
dbo.VectorDistance(@x, @y, ' + @shapefile + '.x1, ' + @shapefile + '.y1, ' + @shapefile + '.x2, ' + @shapefile + '.y2, ' + @shapefile + '.vx, ' + @shapefile + '.vy) AS distance
FROM' + @shapefile + '_Cand INNER JOIN
' + @shapefile + ' ON ' + @shapefile + '_Cand.coid = ' + @shapefile + '.oid INNER JOIN
(
SELECTcoids.coid.value(''@coid[1]'', ''int'') AS coid
FROM@coids.nodes(''//coids/coid'') AS coids(coid)
) AS coids ON ' + @shapefile + '_Cand.oid = coids.coid
'
EXEC sp_executesql @script, N'@x float, @y float, @coids xml', @x, @y, @coids
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
The SELECT statement is a bit difficult to read, so I rewrote it below:
SELECTShapefile_Cand.oid AS coid, Shapefile.oid AS nextcoid, Shapefile.ux, Shapefile.uy,
dbo.VectorDistance(@x, @y, Shapefile.x1, Shapefile.y1, Shapefile.x2, Shapefile.y2, Shapefile.vx, Shapefile.vy) AS distance
FROMShapefile_Cand INNER JOIN
Shapefile ON Shapefile_Cand.coid = Shapefile.oid INNER JOIN
(
SELECTcoids.coid.value('@coid[1]', 'int') AS coid
FROM@coids.nodes('//coids/coid') AS coids(coid)
) AS coids ON Shapefile_Cand.oid = coids.coid
The VectorDistance function returns a float. I don't know if this affects the order, but I'm mostly concerned with the first column in the SELECT clause "coid". I want the SELECT statement to return the rows with "coid" in the same order as the "coids.coid" in the xml parameter @coids. Without any ORDER BY clause, it does this fine most of the time. But occasionally, I get it returned in a different order and that's a problem for me.
If the lack of an ORDER BY clause does not guarantee me the order in any way, does anyone have a suggestion as to how I can get it to return in the order of appearance in the xml parameter @coids? I'm thinking I would have to add an extra field in the xml structure to sort by. If there is an easier, please let me know.
Thanks in advance for any help.
March 21, 2008 at 1:02 pm
You cannot guarantee order without an ORDER BY (or some other cursor method).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2008 at 1:23 pm
You could add the row_number function to your @coids.nodes. Then you can order by coids.rowNumber asc, in the outer query.
March 21, 2008 at 1:32 pm
something like this.
SELECT Shapefile_Cand.oid AS coid, Shapefile.oid AS nextcoid, Shapefile.ux, Shapefile.uy,
dbo.VectorDistance(@x, @y, Shapefile.x1, Shapefile.y1, Shapefile.x2, Shapefile.y2, Shapefile.vx, Shapefile.vy) AS distance
FROM Shapefile_Cand
INNER JOIN Shapefile
ON Shapefile_Cand.coid = Shapefile.oid
INNER JOIN(
SELECT ROW_NUMBER() OVER(ORDER BY coids.coid.value('@coid[1]', 'int')) AS [RowNbr],
coids.coid.value('@coid[1]', 'int') AS coid
FROM @coids.nodes('//coids/coid') AS coids(coid)
) AS coids
ON Shapefile_Cand.oid = coids.coid
ORDER BY coids.RowNbr ASC
March 21, 2008 at 2:02 pm
Thanks for the clarifications and suggestions, rbarryyoung and Adam Haines.
March 21, 2008 at 4:01 pm
Happy to help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply