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)
AS
/*======================================================================
========================================================================*/
SELECT SamplingGroup =
CASE TSAANLYT.CODE
WHEN '1040' THEN 'NITRATE'
WHEN '1035' THEN 'IOC'
WHEN '2987' THEN 'VOC'
WHEN '1094' THEN 'ASB'
WHEN '2050' THEN 'SOC'
WHEN '1005' THEN 'ARS'
WHEN '1041' THEN 'NITRITE'
WHEN '4000' THEN 'RADS'
END,
CONVERT(varchar(12),TSASAMPL.COLLLECTION_END_DT, 107) AS SampDateText,
TINWSF.ST_ASGN_IDENT_CD AS EorSID, TINWSF.NAME AS EorSname,
CONVERT(varchar(12),TSASAMPL.D_FIRST_IDENTED_TS,107) AS
RecvDate, TSAANLYT.CODE AS AnalyteCode, TSASAMPL.COLLLECTION_END_DT AS SampDate
FROM orv80.dbo.TINWSF TINWSF LEFT OUTER JOIN
orv80.dbo.TINWSYS TINWSYS ON TINWSF.TINWSYS_IS_NUMBER =
TINWSYS.TINWSYS_IS_NUMBER AND
TINWSF.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE RIGHT OUTER JOIN
orv80.dbo.TSAANLYT TSAANLYT INNER JOIN
orv80.dbo.TSASAR TSASAR ON TSASAR.TSAANLYT_IS_NUMBER =
TSAANLYT.TSAANLYT_IS_NUMBER INNER JOIN
orv80.dbo.TSASAMPL TSASAMPL ON TSASAMPL.TSASAMPL_IS_NUMBER =
TSASAR.TSASAMPL_IS_NUMBER AND
TSASAMPL.TSASAMPL_ST_CODE = TSASAR.TSASAMPL_ST_CODE ON
TINWSYS.TINWSYS_IS_NUMBER = TSASAMPL.TINWSYS_IS_NUMBER AND
TINWSYS.TINWSYS_ST_CODE = TSASAMPL.TINWSYS_ST_CODE
WHERE (TSAANLYT.CODE IN ('1040', '1035', '2987', '1094', '2050', '1005',
'1041','4000')) AND (TINWSF.TYPE_CODE IN ('SS', 'DS')) AND
(TINWSF.ACTIVITY_STATUS_CD = 'A') AND [EMAIL PROTECTED]
AND ( (TSASAR.DATA_QUALITY_CODE = 'V') AND (TSASAR.CONCENTRATION_MSR = 1) OR
(TSASAR.DATA_QUALITY_CODE = 'V') AND (TSASAR.CONCENTRATION_MSR =
0) OR
(TSASAR.DATA_QUALITY_CODE = 'A') AND (TSASAR.CONCENTRATION_MSR =
0))
--ORDER BY TSASAMPL.COLLLECTION_END_DT DESC, TSAANLYT.CODE ASC
----End of Marker Chems for SDWIS (ORV80)
UNION
--Beginning of MARKER CHEMS FOR ORARCHIVE - Asbestos
SELECT 'Asbestos' AS SamplingGroup, CONVERT(varchar(12),SampDate,107) AS
SampDateText, 'DIST-A' AS EorSID, 'Distribution A' as EorSname,
CONVERT(varchar(12), RecvDate,107), '1094' AS AnalyteCode, SampDate
FROM ORARCHIVE.DBO.Asbestos ASBESTOS
WHERE [EMAIL PROTECTED]
/*=========--ORDER BY SampDate DESC, TESTSUITE DESC==
Beginning of MARKER CHEMS FOR ORARCHIVE - Asbestos
=====================================================*/
UNION
(SELECT
SamplingGroup =
CASE CHEMMON2.ContamID
WHEN '1040' THEN 'NITRATE'
WHEN '1035' THEN 'IOC'
WHEN '2987' THEN 'VOC'
WHEN '1094' THEN 'ASB'
WHEN '2050' THEN 'SOC'
WHEN '1005' THEN 'ARS'
WHEN '1041' THEN 'NITRITE'
WHEN '4000' THEN 'RADS'
END,
CONVERT(varchar(12),SampDate,107) AS SampDateText,
EORSID =
CASE LEN(CHEMMON2.EORSID )
WHEN 1 THEN 'EP-'+ LEFT (CHEMMON2.EORSID,1)
WHEN 2 THEN 'SRC-'+ LEFT(CHEMMON2.EORSID,1)
END,
EorSName, CONVERT(varchar(12), RecvDate, 107), ContamID AS AnalyteCode,SampDate
FROM ORARCHIVE.DBO.CHEMMON2 CHEMMON2
WHERE CHEMMON2.ContamID IN ('1040', '1035', '2987', '1094', '2050', '1005',
'1041','4000')
AND [EMAIL PROTECTED]
)
ORDER BY EorSID ASC, SamplingGroup ASC, SampDate DESC
GO
---------------------------------
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
