stored procedure not showing a result

Question

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

Answer 1

Stop using things like varchar without specifying the length. Watch what happens here:

CREATE PROCEDURE [dbo].[Reconciliation_broken]
    @Param1 varchar
AS
BEGIN
    SET NOCOUNT ON;
    PRINT @Param1;
END
GO
EXEC dbo.Reconciliation_broken @Param1 = 'what happened?';

Results:

w

When you don't specify the length, you get silent truncation - in some cases it is 30 characters; in this case, it is 1 character. There is no error message or warning - in fact if you were inserting data here you would be inserting 1 character and who knows how long you would be doing that before you realized. That data would be lost with no hope of recovery.

So, change those parameters to match the data type and length of the underlying columns. For more information, see:

• Bad habits to kick : declaring VARCHAR without (length)