Parameter lists

In my data set I want to use a where clause with IN. E.g where name in (
'a', 'b', 'c')
But I don't want to hard code the list I want the user to enter it as a
parameter. Does SSRS support parameter lists?
where name in ( @.names )
--
McGeeky
http://mcgeeky.blogspot.comI am aifraid, since now NO, but there will be an option in SQL Server 2005.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"McGeeky" <anon@.anon.com> schrieb im Newsbeitrag
news:uK5sv3xdFHA.2880@.TK2MSFTNGP10.phx.gbl...
> In my data set I want to use a where clause with IN. E.g where name in (
> 'a', 'b', 'c')
> But I don't want to hard code the list I want the user to enter it as a
> parameter. Does SSRS support parameter lists?
> where name in ( @.names )
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>sql

Parameter in WHERE clause using Functions

I'm trying to use a report parameter in my where clause to compare against
the month portion of a field. Reporting Services (version 2000) does not seem
to like this. Here's the WHERE clause I'm trying to get to work (of course
it's all on one line):
WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
(YEAR(close_time) = @.DSYear1)
When I try to execute the query, I get an error that states:
Invalid object name "dbo.RAW_DW.MONTH"
Invalid object name "dbo.RAW_DW.YEAR"
It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
trying to use the MONTH and YEAR functions to extract that data from the
datetime field (close_time) and compare it to the report parameter. It may
just be a formatting issue on my part.
Does anyone know what's wrong with the clause?
Thanks in advance.
BrendaHi Brenda,
I dont think that the error you are getting is related to the WHERE clause
in this case. Do you have RAW_DW.Month and ...YEAR in your overall query?
What are the data types of the two parameters, @.DSMonth1 and @.DSYear1?
Without seeing the full query, it is hard to troubleshoot the error, if
indeed these are valid objects in your database.
Rodney Landrum
"BLKeller" <BLKeller@.discussions.microsoft.com> wrote in message
news:69A3593E-8832-46BB-ADD1-A1079829C95E@.microsoft.com...
> I'm trying to use a report parameter in my where clause to compare against
> the month portion of a field. Reporting Services (version 2000) does not
> seem
> to like this. Here's the WHERE clause I'm trying to get to work (of course
> it's all on one line):
> WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
> (YEAR(close_time) = @.DSYear1)
> When I try to execute the query, I get an error that states:
> Invalid object name "dbo.RAW_DW.MONTH"
> Invalid object name "dbo.RAW_DW.YEAR"
> It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
> trying to use the MONTH and YEAR functions to extract that data from the
> datetime field (close_time) and compare it to the report parameter. It may
> just be a formatting issue on my part.
> Does anyone know what's wrong with the clause?
> Thanks in advance.
> Brenda

paramater values inside IN clause

I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql

Paging query bugs out when adding a where clause

I am getting incorrect results from my paging query, where the same results are being returned multiple times.

Here are two queries I have found to bring the same results:

select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 620 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))

select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 640 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))

When I remove the catname_cst where clause it brings back results properly (i.e. records 622-642 and 643-663).

What is wrong with my where clause that is causing identical data to be returned?

I'll try to be a bit more generic with my question.

How would you properly add a where clause in the T-SQL paging query technique I am using?

The template for the paging query I am using shows up in a few tutorials, it's a fairly known technique:

SELECT TOP rows_to_return * FROM table WHERE unique_id NOT IN (SELECT TOP row_to_start_at unique_id FROM table)

Basically it ignores the rows from Row 0 through row_to_start_at and from that starting point it selects rows until rows_to_return is reached.

So how should a where clause (i.e. where catname_cst='mycategoryname') be properly added to this paging query?

|||

Do a quick google search for "custom paging + stored procedure" and you will find some sample code on how to write custom paging which is much more efficient than what you are doing.

|||

You may be suggesting a tutorial (4guysfromrolla.com?) that uses temp tables and variables for current/last records. If you are, is creating a temp table (with potentially 40,000+ rows to be inserted) really more efficient than selecting the top X rows in my technique? I realize that my technique will become progressively slower as the Y (rows to be ignored) value increases, but I don't believe it should ever exceed the time required in creating a temp table that houses every single row.

I will test this out tomorrow and see how it works.

|||

There is no ORDER BY to go with your TOP so you will get just 20 rows in no particular order. You might as well use SET ROWCOUNT 20 which is faster than TOP 20. Also look into using EXISTS instead of IN. IN gets internally converted into OR's and may not result in efficient query plans.

|||

I will use SET ROWCOUNT, but I cannot determine how NOT EXISTS will help with filtering rows. The only result from google on the subject shares my confusion.

I'll test out the temp tables technique, but even if it works perfectly, I'll still want to know why my technique isn't working 100% of the time. Efficiency aside, there just doesn't seem to be anything wrong with the query, maybe it's the data.. the first dozen or so pages will look fine, then suddenly every so often an identical page is found, then they start coming in more frequently, and soon enough you're seeing more identical pages than non. This isn't just something I'm seeing on a site grid, I'm getting the same identical results within SQL Server. I think that based on the response and lack of responses it is not something with the query (as that kind of thing would have been pointed out, considering it should just be some simple logic error)..

The strange part is that if I perform a query such as getting the top 40 and the top 20 rows starting after results 620 and 640 respectively, 1-20 of each query will be the same, and 21-40 of the first query will contain the proper data that should be showing up in the second query..

To sum that up.. because I really want an answer:

Query 1 should get 40 results starting after result 620, so it should return 621-660.

Query 2 should get 20 results starting after result 640, so it should return 641-660.

Query 1 returns results 621-660.

Query 2 returns results 621-640.

|||

Could I get some insight into how I would write a paging query with not exists?

|||

The reason you are not getting the right results, as I mentioned above, is using TOP without ORDER BY. Its like saying "give me the top 20 records that match this criteria". There is every chance the same record may show up in the next set or an expected record may not show up at all. TOP X is incomplete by itself, although syntactically correct.

|||

Thanks, I implemented a version of my technique with Order By as you suggest and it works.