Legacy ASP, SQL Server, Paging Stored Procedure - resend

[Bob Barrows]

I'm not sure if this made it so I'm resending it (with correction):

This is the best I could come up with. The only other
solutions I could think of involved cursors or dynamic
sql, both of which I try to avoid.

create procedure getrecords (
@recsrequested tinyint) AS
set nocount on

set rowcount @recsrequested
select personid into #temp from pimples order by personid

set rowcount 0
select distinct personid into #temp2 from #temp

select t1.personid,t1.fname,t1.lname,t2.location
from persons t1 inner join #temp2 t3
on t1.personid = t3.personid inner join pimples t2
on t1.personid = t2.personid

drop table #temp
drop table #temp2


HTH,
Bob Barrows


aj wrote:

> Bob,
> sorry, my mistake it's 5. what I meant was...
> (1x3)+(1X2)
>
> Anyways,
>
> That is the result set....
>
> PersonID FName LName Location
> 2 Bart P Nose
> 2 Bart P Chin
> 2 Bart P Butt
> 3 Bob B Leg
> 3 Bob B Arm
>
> I want two records...
> dbconn.execute("getRecords 2")
>
> The getRecords proc will return the two records but I
> really want it to return 3 records because the third
> record is still PersonID and I want the proc to grab that
> record then stop.
>
> Here is what I want when I ask for 2 records...
> PersonID FName LName Location
> 2 Bart P Nose
> 2 Bart P Chin
> 2 Bart P Butt
>
> sorry for the confusion,
> aj
>

>> -----Original Message-----
>>

>>> -----Original Message-----
>>> sure....
>>>
>>> -------person-------- -------pimple--------
>>> PersonID FName LName Location PersonID
>>> 2 Bart P Nose 2
>>> 3 Bob B Chin 2
>>> Butt 2
>>> Leg 3
>>> Arm 3
>>>
>>>
>>> the query...
>>> select * from person inner join pimple on
>>> person.personid=pimple.pimpleid order by personID
>>>
>>> Which will return 2X5=10 rows

>>
>> Why do you think this query will return 10 rows? it should
>> only return 5 rows which will look like this (once you get
>> rid of the * and list the columns you want it to return):
>>
>> select t1.PersonID,t1.FName,t1.LName,t2.Location
>> from person t1 inner join pimple t2 on
>> t1.personid=t2.pimpleid order by t1.personID
>>
>> PersonID FName LName Location
>> 2 Bart P Nose
>> 2 Bart P Chin
>> 2 Bart P Butt
>> 3 Bob B Leg
>> 3 Bob B Arm
>>

>>>
>>> What I want the stored proc to do is know when to return
>>> records based on the number of records I pass it, plus
>>> more records if the "cursor" is still on an existing
>>> personID. Once it goes to a new PersonID, stop.
>>>

>> Again, how is what you want different from what this join
>> query returns already (see above)? Show me what you want
>> (in tabular form) instead of describing it.
>>
>> Bob Barrows
>> .

[AJ]

Forgot to say thanks, that worked!

>-----Original Message-----
>I'm not sure if this made it so I'm resending it (with

correction):

>
>This is the best I could come up with. The only other
>solutions I could think of involved cursors or dynamic
>sql, both of which I try to avoid.
>
>create procedure getrecords (
>@recsrequested tinyint) AS
>set nocount on
>
>set rowcount @recsrequested
>select personid into #temp from pimples order by personid
>
>set rowcount 0
>select distinct personid into #temp2 from #temp
>
>select t1.personid,t1.fname,t1.lname,t2.location
>from persons t1 inner join #temp2 t3
>on t1.personid = t3.personid inner join pimples t2
>on t1.personid = t2.personid
>
>drop table #temp
>drop table #temp2
>
>
>HTH,
>Bob Barrows
>
>
>aj wrote:

>> Bob,
>> sorry, my mistake it's 5. what I meant was...
>> (1x3)+(1X2)
>>
>> Anyways,
>>
>> That is the result set....
>>
>> PersonID FName LName Location
>> 2 Bart P Nose
>> 2 Bart P Chin
>> 2 Bart P Butt
>> 3 Bob B Leg
>> 3 Bob B Arm
>>
>> I want two records...
>> dbconn.execute("getRecords 2")
>>
>> The getRecords proc will return the two records but I
>> really want it to return 3 records because the third
>> record is still PersonID and I want the proc to grab

that

>> record then stop.
>>
>> Here is what I want when I ask for 2 records...
>> PersonID FName LName Location
>> 2 Bart P Nose
>> 2 Bart P Chin
>> 2 Bart P Butt
>>
>> sorry for the confusion,
>> aj
>>

>>> -----Original Message-----
>>>
>>>> -----Original Message-----
>>>> sure....
>>>>
>>>> -------person-------- -------pimple--------
>>>> PersonID FName LName Location PersonID
>>>> 2 Bart P Nose 2
>>>> 3 Bob B Chin 2
>>>> Butt 2
>>>> Leg 3
>>>> Arm 3
>>>>
>>>>
>>>> the query...
>>>> select * from person inner join pimple on
>>>> person.personid=pimple.pimpleid order by personID
>>>>
>>>> Which will return 2X5=10 rows
>>>
>>> Why do you think this query will return 10 rows? it

should

>>> only return 5 rows which will look like this (once you

get

>>> rid of the * and list the columns you want it to

return):

>>>
>>> select t1.PersonID,t1.FName,t1.LName,t2.Location
>>> from person t1 inner join pimple t2 on
>>> t1.personid=t2.pimpleid order by t1.personID
>>>
>>> PersonID FName LName Location
>>> 2 Bart P Nose
>>> 2 Bart P Chin
>>> 2 Bart P Butt
>>> 3 Bob B Leg
>>> 3 Bob B Arm
>>>
>>>>
>>>> What I want the stored proc to do is know when to

return

>>>> records based on the number of records I pass it, plus
>>>> more records if the "cursor" is still on an existing
>>>> personID. Once it goes to a new PersonID, stop.
>>>>
>>> Again, how is what you want different from what this

join

>>> query returns already (see above)? Show me what you

want

>>> (in tabular form) instead of describing it.
>>>
>>> Bob Barrows
>>> .

>
>
>
>.
>