Return to Question

added 253 characters in body

Source Link

edited Jul 6, 2015 at 17:22

ALTER PROCEDURE [dbo].[Reconciliation2]
(
    @Param1 int,
    @Param2 int,
    @Param3 varchar,
    @Param4 varchar
)
    
AS

BEGIN
    SET NOCOUNT ON; 

    SELECT 
        T.FY AS [FY],
           T.FY_PERIOD AS [Period],
           CONVERT(DATE, CREATED_DATE) AS [Created Date],
           CONVERT(DATE, TRANS_DATE) AS [Trans Date],
           T.TRAN_DOC_TYPE AS [Doc Type],
           TASK_ID AS [Task],
           T.DOC_NO AS [Doc Number],
           LINE_NO AS [Line],
           SEQNO AS [Seq],
           LINE_DESCRIPTION AS [Description],
           SUBSTRING(ACCT_NO,1,2) + '-' +
           SUBSTRING(ACCT_NO,3,5) + '-' +
           SUBSTRING(ACCT_NO,8,5) + '-' +
           SUBSTRING(ACCT_NO,13,6) + '-' +
           SUBSTRING(ACCT_NO,19,4) + '-' +
           SUBSTRING(ACCT_NO,23,4) AS [Account Number],
           DEBIT,
        CREDIT  
   CREDIT 
 FROM       
        DBVW_FIN_TRANSACTIONS_UDR T
    INNER JOIN
        (SELECT    INST_ID,
             INST_ID, FY,
             TRAN_DOC_TYPE, DOC_NO
         FROM  DOC_NO     
FROM             DBVW_FIN_TRANSACTIONS_UDR
         WHERE      
             INST_ID = 'SC00' AND
           FY =@Param1 AND FY = @Param1 
           FY_PERIOD =@Param2 AND FY_PERIOD = @Param2 
             AND ([ACTION] = '' OR [ACTION]='V'[ACTION] = 'V') AND
             AND SYS_GEN_DETAIL_LINE = 0 AND
           TRAN_DOC_TYPE =@Param3 AND TRAN_DOC_TYPE = @Param3 
             AND TASK_ID =@Param4= @Param4
         GROUP BY   
             INST_ID, FY, TRAN_DOC_TYPE, DOC_NO) TRAN_BASE
  ON TRAN_BASE.INST_ID = T.INST_ID AND  
      TRAN_BASE.INST_ID=T.INST_ID AND
                                               TRAN_BASE.FY=TFY = T.FY AND
                                                      TRAN_BASE.TRAN_DOC_TYPE=TTRAN_DOC_TYPE = T.TRAN_DOC_TYPE AND
                                                      TRAN_BASE.DOC_NO=TDOC_NO = T.DOC_NO
    WHERE      
        T.INST_ID = 'SC00' AND
           T.FY =@Param1= @Param1 AND
           T.SYS_GEN_DETAIL_LINE = 0 AND
           T.TRAN_DOC_TYPE =@Param3= @Param3 AND
        TASK_ID = @Param4
 TASK_ID =@Param4
  ORDER BY  
        T.DOC_NO, T.LINE_NO, T.SEQNO
END

ALTER PROCEDURE [dbo].[Reconciliation2]
(
    @Param1 int,
    @Param2 int,
    @Param3 varchar,
    @Param4 varchar
)
    
AS

BEGIN
    SET NOCOUNT ON;
    SELECT T.FY AS [FY],
           T.FY_PERIOD AS [Period],
           CONVERT(DATE, CREATED_DATE) AS [Created Date],
           CONVERT(DATE, TRANS_DATE) AS [Trans Date],
           T.TRAN_DOC_TYPE AS [Doc Type],
           TASK_ID AS [Task],
           T.DOC_NO AS [Doc Number],
           LINE_NO AS [Line],
           SEQNO AS [Seq],
           LINE_DESCRIPTION AS [Description],
           SUBSTRING(ACCT_NO,1,2) + '-' +
           SUBSTRING(ACCT_NO,3,5) + '-' +
           SUBSTRING(ACCT_NO,8,5) + '-' +
           SUBSTRING(ACCT_NO,13,6) + '-' +
           SUBSTRING(ACCT_NO,19,4) + '-' +
           SUBSTRING(ACCT_NO,23,4) AS [Account Number],
           DEBIT,
           CREDIT 
 FROM       DBVW_FIN_TRANSACTIONS_UDR T
INNER JOIN
(SELECT    INST_ID,
           FY,
           TRAN_DOC_TYPE,
           DOC_NO
FROM       DBVW_FIN_TRANSACTIONS_UDR
WHERE      INST_ID = 'SC00' AND
           FY =@Param1 AND
           FY_PERIOD =@Param2 AND
           ([ACTION] = '' OR [ACTION]='V') AND
           SYS_GEN_DETAIL_LINE = 0 AND
           TRAN_DOC_TYPE =@Param3 AND
           TASK_ID =@Param4
GROUP BY   INST_ID, FY, TRAN_DOC_TYPE, DOC_NO) TRAN_BASE
 ON         TRAN_BASE.INST_ID=T.INST_ID AND
           TRAN_BASE.FY=T.FY AND
           TRAN_BASE.TRAN_DOC_TYPE=T.TRAN_DOC_TYPE AND
           TRAN_BASE.DOC_NO=T.DOC_NO
WHERE      T.INST_ID = 'SC00' AND
           T.FY =@Param1 AND
           T.SYS_GEN_DETAIL_LINE = 0 AND
           T.TRAN_DOC_TYPE =@Param3 AND
           TASK_ID =@Param4
ORDER BY  T.DOC_NO, T.LINE_NO, T.SEQNO
END

ALTER PROCEDURE [dbo].[Reconciliation2]
(
    @Param1 int,
    @Param2 int,
    @Param3 varchar,
    @Param4 varchar
)
    
AS

BEGIN
    SET NOCOUNT ON; 

    SELECT 
        T.FY AS [FY],
        T.FY_PERIOD AS [Period],
        CONVERT(DATE, CREATED_DATE) AS [Created Date],
        CONVERT(DATE, TRANS_DATE) AS [Trans Date],
        T.TRAN_DOC_TYPE AS [Doc Type],
        TASK_ID AS [Task],
        T.DOC_NO AS [Doc Number],
        LINE_NO AS [Line],
        SEQNO AS [Seq],
        LINE_DESCRIPTION AS [Description],
        SUBSTRING(ACCT_NO,1,2) + '-' +
           SUBSTRING(ACCT_NO,3,5) + '-' +
           SUBSTRING(ACCT_NO,8,5) + '-' +
           SUBSTRING(ACCT_NO,13,6) + '-' +
           SUBSTRING(ACCT_NO,19,4) + '-' +
           SUBSTRING(ACCT_NO,23,4) AS [Account Number],
        DEBIT,
        CREDIT  
    FROM       
        DBVW_FIN_TRANSACTIONS_UDR T
    INNER JOIN
        (SELECT    
             INST_ID, FY,
             TRAN_DOC_TYPE, DOC_NO
         FROM       
             DBVW_FIN_TRANSACTIONS_UDR
         WHERE      
             INST_ID = 'SC00' 
             AND FY = @Param1 
             AND FY_PERIOD = @Param2 
             AND ([ACTION] = '' OR [ACTION] = 'V') 
             AND SYS_GEN_DETAIL_LINE = 0 
             AND TRAN_DOC_TYPE = @Param3 
             AND TASK_ID = @Param4
         GROUP BY   
             INST_ID, FY, TRAN_DOC_TYPE, DOC_NO) TRAN_BASE ON TRAN_BASE.INST_ID = T.INST_ID AND  
                                                      TRAN_BASE.FY = T.FY AND
                                                      TRAN_BASE.TRAN_DOC_TYPE = T.TRAN_DOC_TYPE AND
                                                      TRAN_BASE.DOC_NO = T.DOC_NO
    WHERE      
        T.INST_ID = 'SC00' AND
        T.FY = @Param1 AND
        T.SYS_GEN_DETAIL_LINE = 0 AND
        T.TRAN_DOC_TYPE = @Param3 AND
        TASK_ID = @Param4
    ORDER BY  
        T.DOC_NO, T.LINE_NO, T.SEQNO
END

Source Link

asked Jul 6, 2015 at 15:58

Emries

stored procedure not showing a result

If I run the query outside the stored procedure, I get results, but I only get the column names as the result after executing the query - even though return value is 0. Any help would be appreciated.

ALTER PROCEDURE [dbo].[Reconciliation2]
(
    @Param1 int,
    @Param2 int,
    @Param3 varchar,
    @Param4 varchar
)
    
AS

BEGIN
    SET NOCOUNT ON;
    SELECT T.FY AS [FY],
           T.FY_PERIOD AS [Period],
           CONVERT(DATE, CREATED_DATE) AS [Created Date],
           CONVERT(DATE, TRANS_DATE) AS [Trans Date],
           T.TRAN_DOC_TYPE AS [Doc Type],
           TASK_ID AS [Task],
           T.DOC_NO AS [Doc Number],
           LINE_NO AS [Line],
           SEQNO AS [Seq],
           LINE_DESCRIPTION AS [Description],
           SUBSTRING(ACCT_NO,1,2) + '-' +
           SUBSTRING(ACCT_NO,3,5) + '-' +
           SUBSTRING(ACCT_NO,8,5) + '-' +
           SUBSTRING(ACCT_NO,13,6) + '-' +
           SUBSTRING(ACCT_NO,19,4) + '-' +
           SUBSTRING(ACCT_NO,23,4) AS [Account Number],
           DEBIT,
           CREDIT 
FROM       DBVW_FIN_TRANSACTIONS_UDR T
INNER JOIN
(SELECT    INST_ID,
           FY,
           TRAN_DOC_TYPE,
           DOC_NO
FROM       DBVW_FIN_TRANSACTIONS_UDR
WHERE      INST_ID = 'SC00' AND
           FY =@Param1 AND
           FY_PERIOD =@Param2 AND
           ([ACTION] = '' OR [ACTION]='V') AND
           SYS_GEN_DETAIL_LINE = 0 AND
           TRAN_DOC_TYPE =@Param3 AND
           TASK_ID =@Param4
GROUP BY   INST_ID, FY, TRAN_DOC_TYPE, DOC_NO) TRAN_BASE
ON         TRAN_BASE.INST_ID=T.INST_ID AND
           TRAN_BASE.FY=T.FY AND
           TRAN_BASE.TRAN_DOC_TYPE=T.TRAN_DOC_TYPE AND
           TRAN_BASE.DOC_NO=T.DOC_NO
WHERE      T.INST_ID = 'SC00' AND
           T.FY =@Param1 AND
           T.SYS_GEN_DETAIL_LINE = 0 AND
           T.TRAN_DOC_TYPE =@Param3 AND
           TASK_ID =@Param4
ORDER BY  T.DOC_NO, T.LINE_NO, T.SEQNO
END

sql-server-2008-r2 stored-procedures