Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

In first step i am creating structure of the table, Second step i am inserting data from one table into the 1st table, third step also i am inserting data from another table to first table, fourth step i am displaying d inserted data. But this procedure is not working. Can u plz tel me where i am goin wrong

CREATE PROCEDURE  [dbo].[ISP_ANNEXURE_10]

@SDATE SMALLDATETIME,@EDATE SMALLDATETIME,           
@SAC AS VARCHAR(60),@EAC AS VARCHAR(60)            
,@SIT AS VARCHAR(100),@EIT AS VARCHAR(100)

AS            

BEGIN          


SELECT PEMAIN.TRAN_CD,PEMAIN.TRAN_DT,PEMAIN.TRAN_NO,           
PEMAIN.BILL_NO,PEMAIN.BILL_DT, PEMAIN.[RULE],
CM_MAST.AC_NM,PEMAIN.CONS_NM,PEMAIN.NET_AMT,PEITEM.QTY,
PEITEM.RATE,PEMAIN.GRO_AMT,   PEITEM.ASSES_AMT,PEITEM.EXAMTPER,PEITEM.EXAMT,PEITEM.CESSPER,PEITEM.CESS_AMT,   PEMAIN.SHCESSPER,PEITEM.SHCESS_AMT,PEITEM.PROD_NM,CM_MAST.ECC_NO,CM_MAST.VEND_NM,
PT_MAST.CHAP_NM 
    INTO #ANNXR10 
    FROM PEMAIN INNER JOIN PEITEM  ON (PEMAIN.TRAN_CD=PEITEM.TRAN_CD AND PEMAIN.TRAN_ID=PEITEM.TRAN_ID) 
    INNER JOIN PT_MAST ON (PEITEM.PROD_CD=PT_MAST.PROD_CD) 
    INNER JOIN CM_MAST ON (PEMAIN.AC_ID=CM_MAST.AC_ID)
    WHERE 1=2



    INSERT INTO #ANNXR10 SELECT PEMAIN.TRAN_CD,PEMAIN.TRAN_DT,PEMAIN.TRAN_NO, PEMAIN.BILL_NO,PEMAIN.BILL_DT,PEMAIN.[RULE],
    CM_MAST.AC_NM,PEMAIN.CONS_NM,PEMAIN.NET_AMT,PEITEM.QTY,PEITEM.RATE,PEMAIN.GRO_AMT,
    PEITEM.ASSES_AMT,PEITEM.EXAMTPER,PEITEM.EXAMT,PEITEM.CESSPER,PEITEM.CESS_AMT,
    PEMAIN.SHCESSPER,PEITEM.SHCESS_AMT,PEITEM.PROD_NM,CM_MAST.ECC_NO,CM_MAST.VEND_NM,PT_MAST.CHAP_NM 
    FROM PEMAIN INNER JOIN PEITEM  ON (PEMAIN.TRAN_CD=PEITEM.TRAN_CD AND PEMAIN.TRAN_ID=PEITEM.TRAN_ID) 
    INNER JOIN PT_MAST ON (PEITEM.PROD_CD=PT_MAST.PROD_CD) 
    INNER JOIN CM_MAST ON (PEMAIN.AC_ID=CM_MAST.AC_ID)


    INSERT INTO #ANNXR10 SELECT    SRMAIN.TRAN_CD,SRMAIN.TRAN_DT,SRMAIN.TRAN_NO,'','',SRMAIN.[RULE],
    CM_MAST.AC_NM,SRMAIN.CONS_NM,SRMAIN.NET_AMT,SRITEM.QTY,SRITEM.RATE,SRMAIN.GRO_AMT,
    SRITEM.ASSES_AMT,SRITEM.EXAMTPER,SRITEM.EXAMT,SRITEM.CESSPER,SRITEM.CESS_AMT,
    SRMAIN.SHCESSPER,SRITEM.SHCESS_AMT,SRITEM.PROD_NM,CM_MAST.ECC_NO,CM_MAST.VEND_NM,PT_MAST.CHAP_NM 
    FROM SRMAIN INNER JOIN SRITEM  ON (SRMAIN.TRAN_CD=SRITEM.TRAN_CD AND SRMAIN.TRAN_ID=SRITEM.TRAN_ID) 
    INNER JOIN PT_MAST ON (SRITEM.PROD_CD=PT_MAST.PROD_CD) 
    INNER JOIN CM_MAST ON (SRMAIN.AC_ID=CM_MAST.AC_ID)


    SELECT A10.TRAN_CD,A10.TRAN_DT,A10.TRAN_NO, A10.BILL_NO,A10.BILL_DT, A10.[RULE],
    A10.AC_NM,A10.CONS_NM,A10.NET_AMT,A10.QTY,A10.RATE,A10.GRO_AMT,
    A10.ASSES_AMT,A10.EXAMTPER,A10.EXAMT,A10.CESSPER,A10.CESS_AMT,
    A10.SHCESSPER,A10.SHCESS_AMT,A10.PROD_NM,A10.ECC_NO,A10.VEND_NM,A10.CHAP_NM 
    FROM #ANNXR10 A10
    WHERE A10.TRAN_CD IN('PE','SR') AND A10.[RULE]='EXCISABLE' 
    AND A10.TRAN_DT BETWEEN @SDATE AND @EDATE
    AND A10.AC_NM BETWEEN @SAC AND @EAC
    AND A10.PROD_NM BETWEEN @SIT AND @EIT


DROP TABLE #ANNXR10


END          
share|improve this question
"not working" is not a really helpful diagnostic. Have you got error messages, nothing displayed, anything else ? – Raphaël Althaus 21 hours ago
by the way, I'm a little bit surprise by the WHERE 1=2 clause... – Raphaël Althaus 21 hours ago
@RaphaëlAlthaus SELECT INTO with impossible WHERE is used to create empty table. TOP 0 can also work. – Nenad Zivkovic 20 hours ago
@NenadZivkovic oh, right, thanks for the info. – Raphaël Althaus 20 hours ago

1 Answer

Try this one -

CREATE PROCEDURE [dbo].[ISP_ANNEXURE_10] 

      @SDATE SMALLDATETIME
    , @EDATE SMALLDATETIME
    , @SAC AS VARCHAR(60)
    , @EAC AS VARCHAR(60)
    , @SIT AS VARCHAR(100)
    , @EIT AS VARCHAR(100)

AS BEGIN

    SELECT  A10.TRAN_CD
        ,   A10.TRAN_DT
        ,   A10.TRAN_NO
        ,   A10.BILL_NO
        ,   A10.BILL_DT
        ,   A10.[RULE]
        ,   A10.AC_NM
        ,   A10.CONS_NM
        ,   A10.NET_AMT
        ,   A10.QTY
        ,   A10.Rate
        ,   A10.GRO_AMT
        ,   A10.ASSES_AMT
        ,   A10.EXAMTPER
        ,   A10.EXAMT
        ,   A10.CESSPER
        ,   A10.CESS_AMT
        ,   A10.SHCESSPER
        ,   A10.SHCESS_AMT
        ,   A10.PROD_NM
        ,   A10.ECC_NO
        ,   A10.VEND_NM
        ,   A10.CHAP_NM
    FROM (
        SELECT  PEMAIN.TRAN_CD
            ,   PEMAIN.TRAN_DT
            ,   PEMAIN.TRAN_NO
            ,   PEMAIN.BILL_NO
            ,   PEMAIN.BILL_DT
            ,   PEMAIN.[RULE]
            ,   CM_MAST.AC_NM
            ,   PEMAIN.CONS_NM
            ,   PEMAIN.NET_AMT
            ,   PEITEM.QTY
            ,   PEITEM.Rate
            ,   PEMAIN.GRO_AMT
            ,   PEITEM.ASSES_AMT
            ,   PEITEM.EXAMTPER
            ,   PEITEM.EXAMT
            ,   PEITEM.CESSPER
            ,   PEITEM.CESS_AMT
            ,   PEMAIN.SHCESSPER
            ,   PEITEM.SHCESS_AMT
            ,   PEITEM.PROD_NM
            ,   CM_MAST.ECC_NO
            ,   CM_MAST.VEND_NM
            ,   PT_MAST.CHAP_NM
        FROM PEMAIN
        JOIN PEITEM ON (PEMAIN.TRAN_CD=PEITEM.TRAN_CD AND PEMAIN.TRAN_ID=PEITEM.TRAN_ID)
        JOIN PT_MAST ON (PEITEM.PROD_CD=PT_MAST.PROD_CD)
        JOIN CM_MAST ON (PEMAIN.AC_ID=CM_MAST.AC_ID)

        UNION ALL

        SELECT  SRMAIN.TRAN_CD
            ,   SRMAIN.TRAN_DT
            ,   SRMAIN.TRAN_NO
            ,   ''
            ,   ''
            ,   SRMAIN.[RULE]
            ,   CM_MAST.AC_NM
            ,   SRMAIN.CONS_NM
            ,   SRMAIN.NET_AMT
            ,   SRITEM.QTY
            ,   SRITEM.Rate
            ,   SRMAIN.GRO_AMT
            ,   SRITEM.ASSES_AMT
            ,   SRITEM.EXAMTPER
            ,   SRITEM.EXAMT
            ,   SRITEM.CESSPER
            ,   SRITEM.CESS_AMT
            ,   SRMAIN.SHCESSPER
            ,   SRITEM.SHCESS_AMT
            ,   SRITEM.PROD_NM
            ,   CM_MAST.ECC_NO
            ,   CM_MAST.VEND_NM
            ,   PT_MAST.CHAP_NM
        FROM SRMAIN
        JOIN SRITEM ON (SRMAIN.TRAN_CD=SRITEM.TRAN_CD AND SRMAIN.TRAN_ID=SRITEM.TRAN_ID)
        JOIN PT_MAST ON (SRITEM.PROD_CD=PT_MAST.PROD_CD)
        JOIN CM_MAST ON (SRMAIN.AC_ID=CM_MAST.AC_ID)
    ) A10
    WHERE A10.TRAN_CD IN ('PE','SR') 
        AND A10.[RULE]='EXCISABLE'
        AND A10.TRAN_DT BETWEEN @SDATE AND @EDATE
        AND A10.AC_NM BETWEEN @SAC AND @EAC
        AND A10.PROD_NM BETWEEN @SIT AND @EIT

END
share|improve this answer
What did he actually miss? – Sivakumar 20 hours ago
@Sivakumar, I can't answer to your question precisely, but, in, this case, I would type the query without using temporary tables. – Devart 19 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.