Found the answer to the first part of my question:
SELECT D_Num = IDENTITY(int, 1, 1),*
INTO temptable
FROM
someViewOrTable
Frank Flynn [EMAIL PROTECTED] wrote:
On 2/19/04 4:09 PM, [EMAIL PROTECTED]
wrote:
From: Karen Resplendo
Date: Thu, 19 Feb 2004 11:37:48 -0800 (PST)
To: [EMAIL PROTECTED]
Subject: Stored procedure with UNION---no unique id, how to page?
I have a stored procedure in msSQL that UNIONs 3 queries. There is no unique
identifier that is sequential that I can use to page on webpage because the
data comes from different tables/databases.
Is there a way to save the resultset into a temp table and add an
@@Identity(autonumber) to it? From that I can select Top 50, greater than last
ID, etc, to do my pageing on web page? The resultset is pretty unique to what
the user wants, so it can only be used by that session.
I hope my problem(database) is understandable.
Here is the stored procedure code, just in case:
CREATE PROCEDURE sp_MarkerChemSummary
@vPWS varchar(5)
Stored Proc deleted
You can, do this (... save the resultset into a temp table...) but it's more
complicated or you can run the whole thing each time and have PHP decide
which rows to show as you page through the results.
If the stored proc is quick (low impact on the DB) I'd suggest doing the
latter - return all of the rows to PHP, have PHP display the first 10 rows
and have the Next 10 button be the submit button of a form with all the
same fields you need to re-run the same query (all hidden) and an off-set
field (which in this example would have 10 in it now). Run the query again
and don't print the first 10 rows - then print 10 - then repeat the trick
with the Next 10 button (which now has the offset of 20)...
If the stored Proc is something you don't want to run over and over again do
as you suggest. But you can't use a temp table though because it will get
confused and if you connect to the DB on a different connection those
results will not be there. (temp tables are pre user and get blown away
when you disconnect). It might work in development but if you scale this to
two web servers you could break this. So here's how I'd do it:
Create a regular table with the columns you expect from the SP, and an
identity column, a web_user (int) and a datetime column.
Have the Stored proc:
- delete from this table where your new datetime column is over 6 hours old
(all the old data), you need to do this or the table will overflow.
- figure a random number that is not in this table (this is your web_user
column).
- load this table set the web_user to the random number and the datetime
column to now in MS SQL getdate().
- Have your Stored Proc return only the web_user value to PHP.
Now run a new query on the table where web_user = the value you just got.
Your Next 10 button only needs to store the web_user value and the number
of the row you're on now. If you need some sample code I can send you some
but really it's pretty straight forward in the PHP side.
Good Luck,
Frank
--
Frank Flynn
Poet, Artist Mystic
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
-
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
