多表关联存储过程

时间：2011-05-11 来源：lucky.dai

USE [QEPMS_Test3]
GO
/****** Object: StoredProcedure [dbo].[PRC_RPT_100] Script Date: 05/11/2011 17:10:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PRC_RPT_100]
-- Add the parameters for the stored procedure here
@PROJ_NO varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- start process tempTableR100
CREATE TABLE #tempTableR100 (projNo NVARCHAR(9) Collate Chinese_Taiwan_Stroke_CI_AS)

DECLARE @iPosition char(10)
DECLARE @FName varchar(5000)
DECLARE @LName varchar(5000)

SET @FName = @PROJ_NO
SET @iPosition = ','
IF (@FName is not null)
  BEGIN
   WHILE (@FName <> '')
    BEGIN
     SET @iPosition = CHARINDEX(',', @FName)
                 IF (@iPosition > 1)
      BEGIN
       SET @LName = Left(@FName, @iPosition - 1)
                         SET @FName = RIGHT(@FName, LEN(@FName) - @iPosition)
      END
     ELSE
      BEGIN
       SET @LName = @FName
       SET @FName = ''
      END
     IF (Len(@LName) <> 0)
      BEGIN
       INSERT INTO #tempTableR100(projNo) Values(@LName)
--      select @LName
      END
    END
  END
-- end process tempTableR100

-- Insert statements for procedure here
SELECT PROJ.PR_APP_DATE, PROJ.PR_ELIGIBLE_DATE, CAL.CA_DATE_FM, CAL.CA_DATE_TO,
PROJ.PR_PROJ_NO, APT.AT_DESC_E
--Edited by William 11 Mar 2009
-- , SCAT.SC_DESC_E
, SCLASS.SU_DESC_E
--End edited by William 11 Mar 2009
, PROJ.PR_TITLE_E, PROJ.PR_TITLE_C,
PROJ.PR_ORG_GRANT, PROJ.PR_START_DATE, PROJ.PR_END_DATE, BEN.BEN_DESC_E, APPSC.AS_DESC_E,
--Edited by Simon 09 Mar 2009
-- LTRIM(RTRIM(PROJ.PR_APP_TYPE1 + ' ' + PROJ.PR_APP_TYPE2)) AS APP_TYPE,
TY.TY_DESC_E AS APP_TYPE,
--End edited by Simon 09 Mar 2009
APP.AP_ABBR_NAME, APP.AP_NAME_C, APP.AP_SCH_NO, SESS.SE_DESC_E,
LTRIM(RTRIM(APP.AP_HEAD_ADDR_E + ' ' + APP.AP_HEAD_SURNAME_E + ' ' + APP.AP_HEAD_NAME_E)) AS HEAD_NAME_E,
LTRIM(RTRIM(APP.AP_HEAD_SURNAME_C + ' ' + APP.AP_HEAD_NAME_C + ' ' + APP.AP_HEAD_ADDR_C)) AS HEAD_NAME_C,
LTRIM(RTRIM(APP.AP_ADD1_E + ' ' + APP.AP_ADD2_E + ' ' + APP.AP_ADD3_E + ' ' + APP.AP_REGION_E)) AS HEAD_ADDR_E,
LTRIM(RTRIM(APP.AP_REGION_C + ' ' + APP.AP_ADD1_C + ' ' + APP.AP_ADD2_C + ' ' + APP.AP_ADD3_C)) AS HEAD_ADDR_C,
APP.AP_TEL_O, APP.AP_TEL_H, LTRIM(RTRIM(APP.AP_FAX)) AS AP_FAX, APP.AP_EMAIL,
LTRIM(RTRIM(PROJ.PR_LEA_ADDR_E + ' ' + PROJ.PR_LEA_SURNAME_E + ' ' + PROJ.PR_LEA_NAME_E)) AS LEADER_NAME_E,
LTRIM(RTRIM(PROJ.PR_LEA_SURNAME_C + ' ' + PROJ.PR_LEA_NAME_C + ' ' + PROJ.PR_LEA_ADDR_C)) AS LEADER_NAME_C,
PROJ.PR_LEA_POST, PROJ.PR_LEA_TEL_O, PROJ.PR_LEA_TEL_H, PROJ.PR_LEA_FAX, PROJ.PR_LEA_EMAIL,
LTRIM(RTRIM(PROJ.PR_PRV_PROJNO1 + ' ' + PROJ.PR_PRV_PROJNO2 + ' ' + PROJ.PR_PRV_PROJNO3)) AS LATEST_PROJ_LIST,
(Select [dbo].[FN_GET_BUILD_ON_BO_BUILD_ON](PROJ.PR_PROJ_NO)) AS PRE_FUNDED_PROJ_LIST,
(SELECT UP1.UP_ORG_FILENAME + '(' + CAST(UP1.UP_SIZE AS VARCHAR) + ' Mbytes)'
FROM [dbo].[ESC_ATTACHMENT] ATT1
INNER JOIN [dbo].[ESC_UPLOAD] UP1 ON UP1.UP_REF_NO = ATT1.AT_PROJ_NO
WHERE ATT1.AT_PROJ_NO = PROJ.PR_PROJ_NO
AND (ATT1.AT_CODE = 5)
AND (UP1.UP_SYS_FILENAME = ATT1.AT_NAME)
AND (ATT1.AT_VALID = '1')
AND (ATT1.AT_DELETED = '0')
AND (isnull(UP1.UP_VIRUS,'')<>'1')
) AS PROJ_SUM_FILE,
(SELECT UP2.UP_ORG_FILENAME + '(' + CAST(UP2.UP_SIZE AS VARCHAR) + ' Mbytes)'
FROM [dbo].[ESC_ATTACHMENT] ATT2
INNER JOIN [dbo].[ESC_UPLOAD] UP2 ON UP2.UP_REF_NO = ATT2.AT_PROJ_NO
WHERE ATT2.AT_PROJ_NO = PROJ.PR_PROJ_NO
AND (ATT2.AT_CODE = 6)
AND (UP2.UP_SYS_FILENAME = ATT2.AT_NAME)
AND (ATT2.AT_VALID = '1')
AND (ATT2.AT_DELETED = '0')
AND (isnull(UP2.UP_VIRUS,'') <>'1')
) AS PROJ_PRO_FILE

FROM [dbo].[ESC_PROJ] PROJ
LEFT JOIN [dbo].[ESC_APPLICANT] APP ON APP.AP_APP_CODE = PROJ.PR_APP_CODE
LEFT JOIN [dbo].[ESC_CALL] CAL ON CAL.CA_CALL_NO = PROJ.PR_CALL_NO
LEFT JOIN [dbo].[ESC_APP_TYPE_CODE] APT ON APT.AT_CODE = PROJ.PR_APP_TYPE
--Edited by William 11 Mar 2009
-- LEFT JOIN [dbo].[ESC_SUBCAT] SCAT ON SCAT.SC_CODE = PROJ.PR_SUBCAT
LEFT JOIN [dbo].[ESC_SUBCLASS] SCLASS ON SCLASS.SU_CODE = PROJ.PR_SUBCLASS
--End edited by William 11 Mar 2009
LEFT JOIN [dbo].[ESC_BEN_SECTOR] BEN ON BEN.BEN_CODE = PROJ.PR_SERV_SECT
LEFT JOIN [dbo].[ESC_APP_SECTOR_CODE] APPSC ON APPSC.AS_CODE = APP.AP_APPCAT
LEFT JOIN [dbo].[ESC_SESSION] SESS ON SESS.SE_CODE = APP.AP_SESSION
--Edited by Simon 09 Mar 2009
LEFT JOIN [dbo].[ESC_TYPE]TY ON TY.TY_CODE=APP.AP_TYPE
--End edited by Simon 09 Mar 2009

--WHERE PROJ.PR_PROJ_NO = @PROJ_NO
WHERE PROJ.PR_PROJ_NO IN (SELECT * FROM #tempTableR100)
DROP TABLE #tempTableR100
END