Limit results - TOP in 6.5?? - SQL Server Forums

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

Transact-SQL (2000)

Limit results - TOP in 6.5??

Author

Topic

DanNZ
Starting Member

4 Posts

Posted - 2009-07-31 : 04:51:49

Hi,

I am trying to limit the number of rows returned by a query.

I am using compatibility level 65, so TOP cannot be used (true??)

Suggested alternative is ROWCOUNT, but this will achieve different results.
Would work fine for "SET ROWCOUNT 100; SELECT * FROM contracts; SET ROWCOUNT 0"

But would return incorrect results in some scenarios e.g temp tables..
-------------------
SET ROWCOUNT 100

CREATE TABLE #transactionsTEMP( transId int, customerName varchar(255))
INSERT INTO #transactionsTEMP
SELECT transId,customerName FROM transactions

SELECT transId FROM #transactionsTEMP WHERE customeName = 'Bob'

SET ROWCOUNT 0
------------------
This query would set the ROWCOUNT which would be functional throughout the query.
I have thousands of script files and so do not want to be going into each individually, I am aiming to batch modify all the scripts as limited versions to something like...
"SET ROWCOUNT 10
<orginal script>
SET ROWCOUNT 0"

Hope this makes sense, any help would be greatly appreciated.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-07-31 : 06:56:51

Can you change the compatibility to 7.0 or higher? If not, why not?

And I don't understand "I have thousands of script files and so do not want to be going into each individually, I am aiming to batch modify all the scripts...". You'd have to modify them all anyway whether you use ROWCOUNT or TOP. A batch modification using TOP would have to address the individual SELECT statements anyway, so it would just as easy to wrap them in SET ROWCOUNT.

DanNZ
Starting Member

4 Posts

Posted - 2009-07-31 : 07:12:02

I cannot change the compatibility as any changes to the databases require impact analysis,restore points etc.

Sorry to be unclear. I am happy to modify the script files (or copies of) in the sense that pre pending and appending lines is fine, but I don't want to have to go into every file and asses where to put limiters for that particular file.

So my aim is to limit the maximum result set to 10 rows without modifying the inner workings of script.

Thanks
Dan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-07-31 : 07:21:16

Well I'm fresh out of ideas then. It does sound like you'd only need to do this on the last SELECT of the script. If that's true, yeah it's a lot of grunt work, but it's not that difficult.

DanNZ
Starting Member

4 Posts

Posted - 2009-07-31 : 07:29:27

Thanks for your thoughts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources