473,320 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

inconsistent order by using insert into in a stored procedure

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_table
FROM spm_projects r, spm_agreement_types a, spm_purpose_types
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_number%' SELECT @sortBy2 = @sortBy2 +
', project_number'

-- get projects
EXEC ('INSERT INTO #project_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
FROM spm_projects r, spm_agreement_types a, spm_purpose_types
pu, spm_sponsors sp, spm_sponsors pr, spm_pis p, spm_orgs o
WHERE r.agreement_type_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_table SET @rowCount = row = @rowCount + 1

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

-- paging
SELECT @firstRec = (@Page - 1) * @RecsPerPage
SELECT @lastRec = (@Page * @RecsPerPage + 1)
SELECT @more = COUNT(*) FROM #project_temp_table WHERE row >=
@LastRec
SELECT @total = COUNT(*) FROM #project_temp_table
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_table
WHERE row > @firstRec AND row < @lastRec

DROP TABLE #project_temp_table
Jul 20 '05 #1
7 10188
> 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.
Jul 20 '05 #2
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***@TRASHaspfaq.com> wrote in message
news:ue**************@TK2MSFTNGP09.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.

Jul 20 '05 #3
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*****@noincoming.com> wrote in message
news:Oq**************@tk2msftngp13.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***@TRASHaspfaq.com> wrote in message
news:ue**************@TK2MSFTNGP09.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.


Jul 20 '05 #4
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***@TRASHaspfaq.com> wrote in message news:<ue**************@TK2MSFTNGP09.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.

Jul 20 '05 #5
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(SQL)
if conn.errors.count > 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.nextrecordset()

"Alex Vorobiev" <sa***@mathforum.com> wrote in message
news:e9**************************@posting.google.c om...
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

Jul 20 '05 #6
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
Jul 20 '05 #7
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".
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: traceable1 | last post by:
I am inserting data rows into a table via a stored procedure. After the inserts, I query the rows in the table and I want them to spit back out in the same order I put them in. However,...
4
by: Dan | last post by:
I've encountered some strange behavior in a recursive procedure I'm writing for a bill of materials. First let me ask directly if what I think is happening is even possible: It seems like the...
9
by: joun | last post by:
Hi all, i'm using this code to insert records into an Access table from asp.net, using a stored procedure, called qry_InsertData: PARAMETERS Long, Long, Text(20), Long, DateTime; INSERT...
104
by: Beowulf | last post by:
I have the view below and if I use vwRouteReference as the rowsource for a combo box in an MS Access form or run "SELECT * FROM vwRouteReference" in SQL Query Analyzer, the rows don't come through...
5
by: chloe.crowder | last post by:
Hi I have an oddity. If I run a piece of SQL: SELECT EmployeeNo, MailTo FROM ST_PPS.dbo.Employee where AddedOn BETWEEN '01-jan-2006' and '01-feb-2006' AND MailTo NOT IN ( '3', 'x') order...
1
by: ILCSP | last post by:
Hello, I'm trying to accomplish 3 things with one stored procedure. I'm trying to search for a record in table X, use the outcome of that search to insert another record in table Y and then exec...
2
by: syntego | last post by:
We commonly use triggers to log changes to our main tables to historical log tables. In the trigger, we create a concatenated string of the old values by casting them as follows: ...
1
by: sheenaa | last post by:
Hello Members, I m creating my application forms in ASP.Net 2005 C# using the backend SQL Server 2005. What i have used on forms :: ? On my first form i have used some...
6
by: insirawali | last post by:
Hi all, I have this problem, i need to know is there a way i cn use the data adapter's update method in this scenario. i have 3 tables as below create table table1{ id1 int identity(1,1)...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.