Multiple result set/cursor question for centralize

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)

Multiple result set/cursor question for centralize

Author

Topic

fwhitlark
Starting Member

4 Posts

Posted - 2002-08-29 : 20:16:34

Attention T-SQL hax0rs and gurus!!!! I'd like to solicit your help and expertise on a T-SQL problem I have.

A while ago I created a stored procedure for MSSQL as a fully abstracted, query-independant function to performing paging against any query that returned a result set. The function is very handy for creating web-based administrative interfaces that break up large data sets into multiple pages.

What makes my system better than most page-scrolling functions is that it is completely flexible and works with any(?) query. All page scrolling code I've seen is customized to a single task (a particular table with particular columns). I've tried to make my solution something of an extension to the T-SQL API, much in the way MySQL has the dandy LIMIT clause on the SELECT statement (see bottom of http://www.juicystudio.com/tutorial/mysql/select.html). I've tested the procedure against very complex queries with good results.

Ok, so here is the code for my procedure called sp_LimitSelect:

========================================

/*
Created by Frederick D. Whitlark, 08/06/2001

sp_LimitSelect is a Microsoft SQL Server T-SQL stored procedure used for returning a query result set with a limit and an offset.

Syntax:
sp_LimitSelect query, offset, limit

Example:
sp_LimitSelect 'Select * From users', 10, 5
This call would return 5 rows starting from the 10th row

Notes:
For offsets, the first row of the set is zero.
*/

CREATE PROCEDURE sp_LimitSelect
@query VARCHAR (1024), -- SQL query, it'd better be a SELECT!
@limit INT, -- limit the result set of the query
@offset INT -- start result set from offset
AS
-- Execute call to declare a global cursor (node_cursor) for the query passed to the SP
EXEC (
'DECLARE node_cursor CURSOR
GLOBAL
SCROLL
STATIC
READ_ONLY
FOR ' + @query
)

-- open the global cursor declared above
OPEN node_cursor

-- tweak the starting values of limit and offset for use in the loop
SET @offset = @offset + 1
SET @limit = @limit - 1

-- advanced the cursor to the offset in the result set
FETCH ABSOLUTE @offset FROM node_cursor

-- counter i
DECLARE @i INTEGER
SET @i = 0

-- loop until limit reached by counter i
WHILE (@i < @limit)
BEGIN
-- fetch the next row in the result set and advance counter i
FETCH NEXT FROM node_cursor
SET @i = @i + 1
END

-- clean finish
CLOSE node_cursor
DEALLOCATE node_cursor

========================================

This procedure works fine (a bit slow on big tables as it uses a DREADED CURSOR!!!! :-) ) but I've got one little problem I'd like you to help me solve....

Using FETCH NEXT FROM node_cursor returns each row of the result in it's own result set. Meaning that if the program returns row results in nice 100-row chunks, that there are 100 results sets for each page that contain one row apiece! D'OH! Now, I've been able to handle this fine programmatically in my application through my DB abstraction layer. I simply have a function that iterates over the multiple result sets and returns them as a single set (array). What I want to do is have the sp_LimitSelect return the row results in A SINGLE RESULT SET. I've spent a lot of time in my T-SQL book, referenced the MSDN library, and generally tinkered around with cursor options and temporary tables, but nothing seems to work.

I don't really mind if the solution involves collecting all the result rows together in a single result set, or if it combines the result sets together before returning the data.

Finally, is there a way I could do this with a temporary table or with subqueries? Would I have better performance if there was a solution that avoided using a cursor? I've tooled around with the concepts quite a bit but I can't get the abstractions worked out. All the solultions seem locked down (dependant) on a specific table and certain columns. I need something that allows me to pass ANY QUERY to the procedure and get a paged result set back.

On a side note, here are others' solutions to the T-SQL paging problem in general:

http://www.aspfaqs.com/webtech/062899-1.shtml
http://www.sqlteam.com/item.asp?ItemID=566

These solutions seem to work fine, but functionality HAS TO BE DUPLICATED for every table you want to page-separate. I'm a pretty lazy developer and I hate duplicating code. I'm sure you understand.

Thanks for your help and feel free to use my SP! I think you'll find it quite useful for paging applications (remember, you'll have to iterate though the multiple result sets until we get this thing figured out). BTW, some SQLTeam community pros helped my out before with this SP. Props to them for the insight (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11525).

Finally, I'm pretty desparate here so I also posted my problem at http://www.sqlmag.com/forums/messageview.cfm?catid=9&threadid=8338. Just thought I'd let everyone know in case a solution springs up there.

-------------------------
-Vector
fwhitlark@hotmail.com

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-30 : 08:27:26

Hmmmmm ....

All you need to do for paging is add an identity column to a result set. You can do this with the IDENTITY() function ( not @@IDENTITY mind you ).

Since you're running dynamic SQL, you want to pair the use of IDENTITY() with SELECT INTO so that the final query looks like:

SELECT IDENTITY( int, 1, 1 ) as __identity, {select list}
into #temp
from {rest of query}

__identity will now contain a "row number" that can be used for paging.

Jonathan
{0}

fwhitlark
Starting Member

4 Posts

Posted - 2002-08-30 : 11:39:56

regarding the following code:

SELECT IDENTITY( int, 1, 1 ) as __identity, {select list}
into #temp
from {rest of query}

setbasedisthetruepath, I think your solution would work fine, but it would not allow an arbitrary SELECT statement to be passed to the stored procedure as the the code above requires the {select list}. My goal here is a stored procedure that allows paging for a result set for any SELECT query (potenitally with joins, where clauses, etc) against any table.

I'm trying to provide an extension to the SQL Server API that allows for paged results to be easily requested directly from the application server by simply calling the SP and passing in an arbitrary SELECT statement and a LIMIT and OFFSET value.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-30 : 12:47:42

I understand what you're trying to do, and a solution is possible given the framework I provided earlier. Bear in mind that this is a free forum and no one is paid to offer their assistance.

I know it's rough but you could take a few minutes to write code that parses a SELECT statement and breaks it into two pieces: the SELECT clause to which you would append the IDENTITY() function, and the FROM clause to which you would prepend the INTO statement.

FYI, T-SQL is not part of the "SQL Server API". Very few developers interact with it on a daily basis. Your goal is nothing so sophisticated or complex as to require API interaction.

Jonathan
{0}

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-30 : 13:01:21

I can't believe that no one has written an extended procedure that does this yet ... ???

fwhitlark
Starting Member

4 Posts

Posted - 2002-08-30 : 15:10:48

quote:

I know it's rough but you could take a few minutes to write code that parses a SELECT statement and breaks it into two pieces: the SELECT clause to which you would append the IDENTITY() function, and the FROM clause to which you would prepend the INTO statement.

Ok, that seems reasonable. Thanks for the clarification. I wrote parsing code here:

===============

DECLARE @query VARCHAR(1024)
SET @query = 'select CategoryID, CategoryName from categories WHERE categoryID > 2 ORDER BY categoryName'

DECLARE @FromLocation INT
SET @FromLocation = PATINDEX('%FROM%', @query)

DECLARE @SelectClause VARCHAR(1024)
SET @SelectClause = SUBSTRING(@query, 1, @FromLocation - 1)

DECLARE @FromClause VARCHAR(1024)
SET @FromClause = SUBSTRING(@query, @FromLocation + 4, LEN(@query))

SET @SelectClause = REPLACE(@SelectClause, 'SELECT', '')

SELECT @SelectClause
SELECT @FromClause

===============

However, I get an error when I try to append the the Column Selection to the query like this:

SELECT IDENTITY(int, 1, 1) AS page_scroll_id, * [value of @SelectClause] INTO #temp FROM categories

SQL Server complains that it cannot set up a new IDENTITY column on a table that already has an IDENTITY column. Specifically, the error that SQL Server 7.0 spits out to me is:

quote:

Cannot add identity column, using the SELECT INTO statement, to table '#temp', which already has column 'CategoryID' that inherits the identity property.

So I'm at another impass. Any thoughts on how to get SQL Server to not care about multiple IDENTITY columns on the same table?

fwhitlark
Starting Member

4 Posts

Posted - 2002-08-30 : 15:12:51

quote:

I can't believe that no one has written an extended procedure that does this yet ... ???

Word to that. Anyone at Microsoft paying attention to this board? Please add this feature to your spec!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-30 : 15:21:08

FYI, ADO has the capability to page records, so does ADO.Net. Since Yukon will be .Net compatible, this functionality will be available in the next release.

And FWIW, paging records is a presentation issue IMHO, not a database issue; it's not a "missing" feature. If you want to deal with a partial set above and beyond what cursors, TOP and SET ROWCOUNT provide it's something that needs to be done outside of SQL Server. You could very easily wrap this functionality into a COM layer using ADO's capabilities instead of making T-SQL jump through hoops.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-04 : 21:01:50

quote:
I've spent a lot of time in my T-SQL book, referenced the MSDN library, and generally tinkered around with cursor options and temporary tables, but nothing seems to work.

...

I'm a pretty lazy developer and I hate duplicating code. I'm sure you understand.

No, I don't understand. You contradict yourself. Actually, I do understand. You're not a lazy developer. You're much like the stereotypical geek who will spend (waste?) countless hours and energy trying to create something that appears to be the ultimate time-saving device. That's not being lazy. Industrious, perhaps. Inventive, most definitely. But lazy? No. Although that is often the excuse given for it because most people who fall into that behavior pattern have been taunted by their peers to not be productive, and so in an attempt to remain "cool" they focus their efforts in creating a "lazy" tool. But don't worry. You don't have to justify yourself to us.

Have you given any thoughts to the performance penalties a "one-for-all" sproc is likely to incur? You already admit that your solution is "a bit slow on big tables as it uses a DREADED CURSOR!!!!" You'll find that dynamic SQL often is slower than straight SQL, too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources