473,746 Members | 2,283 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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
Jul 20 '05 #1
7 10237
> 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***@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.

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*****@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.


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***@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.

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(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

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
13179
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, apparently, Oracle actually inserts the rows in some type of bulk insert, so they are not in the same order I put them in. I run a COMMIT after each insert, to no avail. Here's kind of an example of what I'm talking about:
4
2580
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 procedure is not following the recursion in serial order, but in parallel. In other words, after one instance of the procedure calls itself, it continues executing lines below the recursion before the recursion is done. Is that possible? I...
9
5276
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 INTO Table ( ID, Cod, CodArt, Q1, DataUscita ) VALUES (pID, pCod, pCod, pQ1, pDataUscita);
104
10884
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 sorted by Numb. Everything I've read on the web suggests that including the TOP directive should enable ORDERY BY in views. Does someone have an idea why the sorting is not working correctly for this particular view? thanks. CREATE VIEW...
5
3025
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 by MailTo
1
2435
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 another stored procedure and use the outcome of that stored procedure to update the record in table Y. I have this stored procedure (stA) CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL) AS
2
11514
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: CAST(O.MYDATE AS CHAR(30)) When directly updating date fields in the main table, the logged value gets saved in the format YYYY-MM-DD as expected.
1
6133
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 label,textboxs,dropdownlists,radiobutton and checkbox asp standard controls. On the click event of the command button the data gets stored into the database. I have created the stored procedures for the insert,update,delete. I have...
6
2944
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) Constraint pk_table1 Primary Key,
0
8975
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9516
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9351
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9286
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9219
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8229
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6062
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4840
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3294
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.