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