Selection block of records problem

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)

Selection block of records problem

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-27 : 18:31:20

Hi.

I have a sql code to select a block of records at a time. For example if I have 51 records in the table, the script is selecting records between 1-26, or 26-50 and so on. The script works perfect but I had a problem with it recently. First, here's the code:

create procedure proc_Forum
@forum int,@BOTTOM int=0,@TOP int=25
as
set nocount on

declare @sql varchar(8000)

set @sql='
select K_ID, K_SonMesajTarihi,K_SonMesajZamani
from KONULAR
where
--the K_Forum column is also in KONULAR table
K_Forum='+convert(varchar,@forum)+'
and
K_ID not in
(select top '+convert(varchar, @BOTTOM)+' K_ID from KONULAR order by K_ID)
and K_ID in
(select top '+convert(varchar, @TOP)+' K_ID from KONULAR order by K_ID)
--order by lastpost date and time
order by K_SonMesajTarihi desc, K_SonMesajZamani desc'

exec(@sql)

"KONULAR" means "topics" and is used to contain the topic information of the forum I'm coding.

I just understood that the code is not a proper one I can use. For example, let's say I want to display first 25 topics for the Forum 1. I would exec the script like this : "exec proc_Forum 1,0,25". The problem is, the code will first select the first 25 records and then select the topics which are for Forum 1 (with using K_Forum). If another forum,like Forum 2, does have two topics in the first 25 record, the result of the proc will display 23 topics of the Forum 1, because of the excluding.

This kind of select's kinda beats my t-sql knowledge, so I thought I could ask you, the guru's at the Sqlteam forum. How can I make it select a block of records, not a block of rows like it's right now?

Nazim
A custom title

1408 Posts

Posted - 2002-01-28 : 03:38:31

i think you missed filtering records on forum_id in your sub query's

Check this

create procedure proc_Forum
@forum int,@BOTTOM int=0,@TOP int=25
as
set nocount on

declare @sql varchar(8000)

set @sql='
select K_ID, K_SonMesajTarihi,K_SonMesajZamani
from KONULAR
where
--the K_Forum column is also in KONULAR table
K_Forum='+convert(varchar,@forum)+'
and
K_ID not in
(
select top '+convert(varchar, @BOTTOM)+' K_ID from KONULAR
where K_Forum='+convert(varchar,@forum)+'
order by K_ID desc'
)
and K_ID in
(

select top '+convert(varchar, @TOP)+' K_ID from KONULAR
where K_Forum='+convert(varchar,@forum)+'
order by K_ID '

)
--order by lastpost date and time
order by K_SonMesajTarihi desc, K_SonMesajZamani desc'

exec(@sql)

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 01/28/2002 03:39:30

kensai
Posting Yak Master

172 Posts

Posted - 2002-01-28 : 14:40:06

Thanks a lot Nazim!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources