Limiting number of resulting rows

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)

Limiting number of resulting rows

Author

Topic

rado
Starting Member

18 Posts

Posted - 2004-03-11 : 04:48:25

Hi everyone,

I need to limit the number of resulting rows to a number specified by variable in my stored procedure, but can't find a good solution to this. It is a stored procedure that returns XML using FOR XML EXPLICIT and the query consists of several SELECT statements joined by UNION ALL, and I need to limit the number of rows of only one SELECT statement.

The problem is:
1. I can't use SELECT TOP x, since I can't use a variable like SELECT TOP @MaxRows
2. I can't use SET ROWCOUNT @MaxRows, as this would affect all SELECT statements joined by UNION ALL

Is there a solution

Thanks in advance

rado

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-11 : 05:20:01

You can take out the query that you want to limit and use a temp table

set rowcount n
select ...
into #a
from ...
set rowcount 0

select * from #a
union all
select ....
union all
select ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rado
Starting Member

18 Posts

Posted - 2004-03-11 : 05:37:37

Seems like a good idea! Thanks!

I was thinking about creating a table type variable rather than a temporary table, but I would like to ask - what performance impact would this solution (creating a termporary table or table type variable) have? This stored procedure will be probably called very often (several thounsands times/day.

Thanks again,
rado

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-11 : 06:00:40

If it's small then a table variable will be best.
A temp table gives more optimisation options though so can be better for large amounts of data.
I would guess if it's being called that often that a table variable would be best.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rado
Starting Member

18 Posts

Posted - 2004-03-11 : 06:58:58

Yes it's small (it will be about 5-15 rows of 2 columns) so I guess I'll use a table variable. Thanks again for your help!

rado

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources