inconsistent order by using insert into in a stored procedure

[Alex Vorobiev]

hi there,

i am using sql server 7. below is the stored procedure that is giving
me grief. its purpose it two-fold, depending on how it is called:
either to return a pageset (based on page number and page size), or to
return IDs of previous and next records (based on current record id).
the problem is, that the order in which records are inserted into the
temp table is inconsistent, even though the calling statement and the
order by is always the same: sometimes records are ordered correctly,
by project_number, and sometimes the order is broken starting at some
record (which is always the same).

i have no idea what is wrong here, i would appreciate any help!
thanks so much.
here is the calling statement:
EXECUTE spProjects 2,null,'project _number','asc', '',6,50

here is the proc:
CREATE PROCEDURE spProjects

@action int,
@currID int,
@sortBy varchar(50),
@sortDir varchar(4),
@searchBy varchar(255),
@Page int,
@RecsPerPage int

AS

SET NOCOUNT ON

DECLARE @nextID int
DECLARE @prevID int
DECLARE @currRow int
DECLARE @rowCount int
DECLARE @firstRec int
DECLARE @lastRec int
DECLARE @total int
DECLARE @more int
DECLARE @sortBy2 varchar(50)

-- setup temp table
SELECT r.id as row, r.*,
a.name agr_type,
pu.name purpose,
sp.name sponsor,
pr.name prime,
p.lname p_lname, p.fname p_fname, p.mname p_mi, p.email
p_email,
o.name org,
convert(varchar (10), r.created_date, 101) adddate_c,
convert(varchar (10), r.updated_date, 101) upddate_c
INTO #project_temp_t able
FROM spm_projects r, spm_agreement_t ypes a, spm_purpose_typ es
pu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs o
WHERE 1 = 0

IF @sortBy IS NULL SELECT @sortBy = 'project_number '
IF @sortBy = '' SELECT @sortBy = 'project_number '
SELECT @sortBy2 = @sortBy + ' ' + @sortDir
IF @sortBy NOT LIKE '%project_numbe r%' SELECT @sortBy2 = @sortBy2 +
', project_number'

-- get projects
EXEC ('INSERT INTO #project_temp_t able
SELECT r.id as row, r.*,
a.name agr_type,
pu.name purpose,
sp.name sponsor,
pr.name prime,
p.lname p_lname, p.fname p_fname, p.mname p_mi, p.email
p_email,
o.name org,
convert(varchar (10), r.created_date, 101) adddate_c,
convert(varchar (10), r.updated_date, 101) upddate_c
FROM spm_projects r, spm_agreement_t ypes a, spm_purpose_typ es
pu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs o
WHERE r.agreement_typ e_id = a.id
AND r.purpose_type_ id = pu.id
AND r.sponsor_id = sp.id
AND r.prime_id *= pr.id
AND r.pi_id = p.id
AND r.org_id = o.id
' + @searchBy + '
ORDER BY ' + @sortBy2)
SET @rowCount = 0

-- number records
UPDATE #project_temp_t able SET @rowCount = row = @rowCount + 1

-- prev/next
SELECT @currRow = row FROM #project_temp_t able WHERE id = @currID
SELECT @prevID = id FROM #project_temp_t able WHERE row = @currRow -
1
SELECT @nextID = id FROM #project_temp_t able WHERE row = @currRow +
1

-- paging
SELECT @firstRec = (@Page - 1) * @RecsPerPage
SELECT @lastRec = (@Page * @RecsPerPage + 1)
SELECT @more = COUNT(*) FROM #project_temp_t able WHERE row >=
@LastRec
SELECT @total = COUNT(*) FROM #project_temp_t able
SET NOCOUNT OFF

-- prev/next
IF @action = 1 SELECT @prevID as prevID, @nextID as nextID

--paging
IF @action = 2
SELECT *, @more as more, @total as total
FROM #project_temp_t able
WHERE row > @firstRec AND row < @lastRec

DROP TABLE #project_temp_t able

[Aaron Bertrand - MVP]

> the problem is, that the order in which records are inserted into the

temp table is inconsistent

INSERT INTO has no defined order. The engine will choose an arbitrary
order, and you can't control it. Sometimes it just happens to work the way
you want, but the behavior is not well-defined... and certainly not
guaranteed.

You are thinking about the inserted table as a physical structure; add an
index, use an order by on *that* table to determine first, next, previous,
last, ranges, etc. Don't rely on the *physical* structure being in the
*logical* order you expect.

[Delbert Glass]

FYI

Actually INSERT INTO does have a defined order.

The order says what sequence
the new info is added to the table.

Meanwhile, the organization of the rows
in the table may or may not have anything
at all to do with what sequence the rows
where created.

Bye,
Delbert Glass

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:ue******** ******@TK2MSFTN GP09.phx.gbl...

the problem is, that the order in which records are inserted into the
temp table is inconsistent
INSERT INTO has no defined order. The engine will choose an arbitrary
order, and you can't control it. Sometimes it just happens to work the

way you want, but the behavior is not well-defined... and certainly not
guaranteed.

You are thinking about the inserted table as a physical structure; add an
index, use an order by on *that* table to determine first, next, previous,
last, ranges, etc. Don't rely on the *physical* structure being in the
*logical* order you expect.

[Aaron Bertrand [MVP]]

I think we're saying the same thing.

A SELECT from an INSERT INTO ... SELECT statement certainly may have an
ORDER BY clause. But how these rows end up in the target table once the
operation has completed is not guaranteed to be in the same order as the
original ORDER BY clause. Again, usually this *is* the case (barring a
clustered index), but like I said before, it is not guaranteed.

"Delbert Glass" <de*****@noinco ming.com> wrote in message
news:Oq******** ******@tk2msftn gp13.phx.gbl...

FYI

Actually INSERT INTO does have a defined order.

The order says what sequence
the new info is added to the table.

Meanwhile, the organization of the rows
in the table may or may not have anything
at all to do with what sequence the rows
where created.

Bye,
Delbert Glass

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:ue******** ******@TK2MSFTN GP09.phx.gbl...

the problem is, that the order in which records are inserted into the
temp table is inconsistent

INSERT INTO has no defined order. The engine will choose an arbitrary
order, and you can't control it. Sometimes it just happens to work the

way

you want, but the behavior is not well-defined... and certainly not
guaranteed.

You are thinking about the inserted table as a physical structure; add an index, use an order by on *that* table to determine first, next, previous, last, ranges, etc. Don't rely on the *physical* structure being in the
*logical* order you expect.

[Alex Vorobiev]

aaron,

i can add an alter column and add a clustered index to the table
before i do insert into select from...

however, the stored procedure returns "... index being rebuilt"
message even though NOCOUNT is set to ON. obviously, this screws up
the ado recordset.

is there any way to disable these messages?

thanks,

--sasha

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message news:<ue******* *******@TK2MSFT NGP09.phx.gbl>. ..

the problem is, that the order in which records are inserted into the
temp table is inconsistent

INSERT INTO has no defined order. The engine will choose an arbitrary
order, and you can't control it. Sometimes it just happens to work the way
you want, but the behavior is not well-defined... and certainly not
guaranteed.

You are thinking about the inserted table as a physical structure; add an
index, use an order by on *that* table to determine first, next, previous,
last, ranges, etc. Don't rely on the *physical* structure being in the
*logical* order you expect.

[Aaron Bertrand [MVP]]

If SET ANSI_WARNINGS OFF doesn't disable them, you can use ado's errors
collection to ignore error numbers in your range. We actually had to do
that for an ASP app that called a stored procedure which, among other
things, swapped out names for a "current" and "archive" metadata table,
using sp_rename. The ASP code to avoid the error was something like this:

on error resume next
set rs = conn.execute(SQ L)
if conn.errors.cou nt > 0 THEN
for each e in conn.errors
if e.NativeError <> 15477 then
Response.Write "Oops"
else
' it's okay, just warnings
end if
next
end if

' you may have to experiment with nextrecordset() :

set rs = rs.nextrecordse t()

"Alex Vorobiev" <sa***@mathforu m.com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...

aaron,

i can add an alter column and add a clustered index to the table
before i do insert into select from...

however, the stored procedure returns "... index being rebuilt"
message even though NOCOUNT is set to ON. obviously, this screws up
the ado recordset.

is there any way to disable these messages?

thanks,

--sasha

[Alex Vorobiev]

aaron, et.al.,

thank you guys for your help...

i just realized, however, that a clustered index won't do it for me.
if i have a dynamic where clause, i would know the name of the column
to index only at runtime.

what i am trying to do has most certainly been done, but i cannot find
a good comprehensive solution. i would like to have dynamic search by
and sort by arguments in a stored procedure, and end up with a
recordset / temp table where records are numbered consecutively based
on the dynamic sort by.

1. if i don't want to spell out my sort_by and search_by vars using
"case when", i need to use dynamic sql via EXEC. i was hoping to do
this using "SELECT INTO" local temp table, but if the SELECT INTO is
wrapped in the EXEC, other calls within my stored proc can't find the
table - i assume, this is a compile time vs runtime scoping issue.

2. i can first define the temp table within the scope of the proc
using CREATE TABLE or a fake SELECT INTO ... WHERE 1 = 0, however i
found if i do subsequent EXEC("SELECT INTO..."), there is nothing in
the previously defined temp table. this may be obvious to most of
you, i am guessing another scoping issue. i guess you can't overwrite
an existing table like that.

furthermore, if i define the table first, and then use INSERT INTO, as
we have already discussed in this thread, records in the table won't
be ordered according to the ORDER BY.

3. adding a clustered index won't help me because i have dynamic
order_by and order_direction

i've thought about doing other things to work around this. i can't
use a regular table because of a possible race condition when multiple
users are using the application. i've thought about using a global
table with a unique name via SPID, but then the table name will also
be a variable, and every statement in my stored proc would have to be
wrapped in EXEC.

from what i can see, i have only two options:

1) write a SELECT INTO, spell out all possible cases of search_by and
sort_by using CASE WHEN, with something like row = identity(int,1, 1)
to generate consecutive numbers for my ordered rows. this way i avoid
dynamic sql, but i have to spell out all search and sort cases, and i
may have other existing identity columns in my SELECT already.

2) leave everything as is, but loop over the entire temp table after
the INSERT INTO, and number the rows within the loop. with
potentially thousands of rows in the table, this may be a significant
performance hit.

hoping for your collective wizdom to come to the rescue, i wonder if
there is another, smarter way to do this.

thanks in advance for your help,

--sasha

[louis nguyen]

Hi Sasha,

If you want dynamic order by, you can do:
ORDER BY
[case statement] ascending,
[case statement] descending,
[case statement] ascending,
[case statement] descending ...

You should have two case statements, for every column you may want to
order by. If your case statement returns [void], sql will ignore
that part of the statement. I've written 3 articles that may interest
you.
http://www.sql-server-performance.co...cles_other.asp. Look for
"Sql techniques for web reporting".