Identity numbering problems on a temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identity numbering problems on a temp table

Author

Topic

jcavaliere
Starting Member

4 Posts

Posted - 2005-05-13 : 13:17:51

Hello all,

I'm writing a stored procedure that creates a temp table to give a row number return with my queries. there is a SQL statement generated based on user unput that searches our users database and returns results. When I have no search parameters, the numbers come out fine, but when a parameter is used, the numbering is off...

ALTER PROCEDURE sp_users_search
@args varchar(500)
AS
SET NOCOUNT ON
CREATE TABLE #RowNumber ( RowNumber int IDENTITY (1, 1), user_id int )

INSERT #RowNumber exec('select user_id from users ' + @args)

exec('select
r.RowNumber,u.user_id,
up.name as img_name,
up.user_photo_id,
u.username,
u.headline,
u.b_day_year_1,
u.sex,
u.city,
u.state,
u.country

from #RowNumber r inner join users u on r.user_id=u.user_id

inner join user_photos up on u.user_id=up.user_id

' + @args)

DROP TABLE #RowNumber
SET NOCOUNT OFF

So if I leave @args='', the RowNumber field returns the correct values, but if I say

@args=' where sex=''Female'' '

the numbers sometimes come back skipping the first or second number in the sequence (2,3,4,5, etc..) or ( 1,3,5, etc...) does that make sense?

Anyone have any ideas as to why it would be doing that?

-Jason

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-13 : 13:28:00

Sex='Female'

You bet....

Sorry...

Ummm Where is the ORDER BY?

And why do you need to worry about a "row number"? It can be different everytime you run that, for the exact same criteria.

Brett

8-)

jcavaliere
Starting Member

4 Posts

Posted - 2005-05-13 : 13:36:12

I'm using the data to populate an ASP.NET repeater control, and we want to display a row number on the page - this is the only way I know of to acomplish that task.

I dind't put an ORDER BY in yet becuase I wanted to keep it simple, but that doesn't seem to be the problem. I took a small set of the full database to work with (50 rows) and I can see that the result is not being returned out of order.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources