Using @@rowcount in a nested query

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)

Using @@rowcount in a nested query

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-07-13 : 10:03:12

I have to return a result set generated from a query to the user front end. The user has the option to choose how many results they wish to display (50,100,150, etc). The application therefore passes the stored procedure the number of records to return.

I wanted to use @@rowcount to limit the results, however, I need to limit the results in the sub select rather than the main query. I know this is not very clear, so I shall explain the problem further:

A user wants to reprint a mobile phone bill. They provide a search criteria, and based on this information, a list of the bills that fulfill the criteria is returned. A user considers a bill as the document they recieved in an envelope. However, we consider a bill to consist of a number of different document types (for example, the summary, the itemised listing of calls, the actual invoice, etc), what we call a 'Document Set'. The database stores all the information about each individual document, and groups the documents together to create a complete bill for the user.

I want to limit the number of complete bills a user can retrieve and not the number of individual documents. Therefore, how can this be done in the following construct:


SELECT * FROM Document 
WHERE DocumentSetID IN 
	(
	SET @@ROWCOUNT = @limit --This is wrong of course 
	SELECT DocumentSetID
	FROM DocumentSet
	WHERE (@accountNumber IS NULL OR AccountNumber = @accountNumber)
	AND (@invoiceNumber IS NULL OR InvoiceNumber = @invoiceNumber)
	AND (@handsetNumber IS NULL OR HandsetNumber = @handsetNumber)
	GROUP BY DocumentSetID
	)

Thanks in advance

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-13 : 10:15:41

try:


SET ROWCOUNT @limit
declare @t2 table as (DocumentSetID int) -- or varchar if that's your column type
insert into @t2 (DocumentSetID)
SELECT DocumentSetID
FROM DocumentSet
WHERE (@accountNumber IS NULL OR AccountNumber = @accountNumber)
AND (@invoiceNumber IS NULL OR InvoiceNumber = @invoiceNumber)
AND (@handsetNumber IS NULL OR HandsetNumber = @handsetNumber)
GROUP BY DocumentSetID

set rowcount 0 -- EDIT: forgot this

SELECT t1.* 
FROM   Document t1
       join @t2 t2 on t1.DocumentSetID = t2.DocumentSetID

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 10:16:10

Create a stored procedure having the rowcount as input parameter


Create Procedure Results (@Rows int,otherparameters...)
as
Set Rowcount @rows
SELECT * FROM Document 
WHERE DocumentSetID IN 
	(
	SELECT DocumentSetID
	FROM DocumentSet
	WHERE (@accountNumber IS NULL OR AccountNumber = @accountNumber)
	AND (@invoiceNumber IS NULL OR InvoiceNumber = @invoiceNumber)
	AND (@handsetNumber IS NULL OR HandsetNumber = @handsetNumber)
	GROUP BY DocumentSetID
	)
Set RowCount 0

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-13 : 10:17:39

madhivanan i don't think this is the same thing... limiting inner and outer select.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 10:30:55

Yes. That is Good Point Mladen
In this case Top operator can also be used with Dynamic SQL
But yours seems to be good approach

Madhivanan

Failing to plan is Planning to fail

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-07-13 : 10:45:48

Thank you both for your replies!

I have gone with your idea Spirit1, and it has worked, now i just have to test it. I was actually thinking on the same lines (which is a first), so finally, I am beginning to learn something.

Thanks again!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources