儲存過程不顯示結果

如果我在儲存過程之外執行查詢，我會得到結果，但我只會在執行查詢後得到列名作為結果——即使返回值為 0。任何幫助都將不勝感激。

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

停止使用varchar不指定長度之類的東西。看看這裡發生了什麼：

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

結果：

w

當您不指定長度時，您會得到靜默截斷 - 在某些情況下是 30 個字元；在這種情況下，它是 1 個字元。沒有錯誤消息或警告 - 事實上，如果您在此處插入數據，您將插入 1 個字元，誰知道在您意識到之前您會這樣做多長時間。這些數據將失去，沒有恢復的希望。

因此，更改這些參數以匹配基礎列的數據類型和長度。有關更多資訊，請參閱：

• 要踢的壞習慣：在沒有（長度）的情況下聲明 VARCHAR

引用自：https://dba.stackexchange.com/questions/106083