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 TINWSYS.TINWSYS_IS_NUMBER=@vPWS
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 PWS=@vPWS
/*=========--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 PWS=@vPWS
)
ORDER BY EorSID ASC, SamplingGroup ASC, SampDate DESC
GO
---------------------------------
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
