practial equivalent to LIMIT when using mssql

sfullman

I'm spoiled by that LIMIT clause as I'm sure the rest of you mySQL guys are.

Problem is, no such thing in M$SQL.

Any PRACTICAL WAY to do the same thing without pulling a gazillion records and cutting it down? I would appreciate any outside references here as well.

I'm learning mssql and look forward to contributing to the forum, on this area soon, but am very grateful for the help I receive.

Sincerely,
Sam Fullman
Compass Point Media

YappyDog

MS SQL will let you limit the number of rows you get back from a query, but not set a starting point.

If you use the set rowcount command like this:

SET ROWCOUNT 100
Select * from MyTable

You'd get the first 100 rows in the table

Use SET ROWCOUNT 0 to turn it back to normal.

sfullman

so,

you're saying that you can go from zero to somewhere but not from somewhere to somewhere else? That's, well, actively wicked.

So, I've got a gazillion records, and I get the first 30 for a display. Is there any way to get 30-60 without getting 60 and getting rid of the first 30.

Microsoft really amuses me sometimes.... :-|

Sam Fullman

YappyDog

That's pretty much the gist of it.

Unless you have some sort of id field that is in the proper order. Then you can set your second query to id > whatever the last one was.

sfullman

Alas, I want the customer to be able to sort on multiple levels on the fields of his choice. Oh well I'll just have to lose that feature in mssql.

Sam

barand

If you store the contents of the fields that are sorted on, you can still do the 'greater than' thing. You may need extra indexes to speed things along.

baronmask

Ummm,
maybe u guys can use what I use?
I use a store procedure as a trick of limit functionality in mysql. When I need a limit function then I called this stored procedure...

Here is my sp:

CREATE PROCEDURE CCFBetaLimitSelectForReportbyQA
(
@query NVARCHAR(2000), -- SQL Query
@ INT, -- page number
@ INT -- limit the result set of the query
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

-- Find out where we will start our records from
DECLARE @RecCount int
SELECT @RecCount = @ * @ + 1

--Create a temporary table
CREATE TABLE #TempReportbyQA
(
ID int IDENTITY,
ccf_id numeric,
ccf_no_old nvarchar(10),
ccf_subject nvarchar(150),
ccf_status nvarchar(4)
)

-- Insert the rows from Query into the temp. table
INSERT INTO #TempReportbyQA (ccf_id, ccf_no_old, ccf_subject, ccf_status)
EXECUTE(@query)

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@ - 1) @
SELECT @LastRec = (@ @ + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT ,
MoreRecords =
(
SELECT COUNT()
FROM #TempReportbyQA TRQA
WHERE TRQA.ID > @LastRec
)
FROM #TempReportbyQA
WHERE ID > @FirstRec AND ID < @LastRec

-- Turn NOCOUNT back OFF

SET NOCOUNT OFF

Then anytime I need this sp, I just called it from my page by setting the starting point and limiting the result...

I guess that what I can share...

Thanks

sfullman

Hi Baronmask,

That looks like you've spent some time on developing it. I'm still new to the mssql side of things. Could you send me an example of this being used? i.e. including some of the php code you'd use.

And by the way, looking at your SP, I'm going to have users doing multiple-field sorts and many tables they own. Will it be possible to handle these with this one SP or do I need an SP for each table.

Thanks,
Sam