分享
三行代码  ›  专栏  ›  技术社区  ›  user3798811

将变量从存储过程传递到另一个存储过程

  •  0
  • user3798811  · 技术社区  · 1 周前

    我有一个存储过程,需要将参数从一个过程传递到另一个过程,并将其显示为输出。我在我的程序[xxx]的标题中声明了以下内容。[zzzz_ERP_Cyyyyy]

     DECLARE @ProcedureLogRowKey INT
     DECLARE @ProcedureRecordCount INT 
     DECLARE @ProcedureStartDateTime DATETIME 
     DECLARE @ProcedureLog_Note NVARCHAR(100)
    
     EXEC [XXX].[spciProcedurePerformanceStartRecord_help]
          '.[xxx].[zzzz_ERP_Cyyyyy]',
          1,
          @ProcedureStartDateTime,
          'Contract Check',
          @ProcedureLogRowKey OUTPUT
    

    Msg 515,16级,状态2,程序spciProcedurePerformanceStartRecord帮助,第33行[批处理开始行17]

    下面是我将变量从中获取并传递到我的过程中的过程[xxx]。[zzzz_ERP_cyyyy]

     CREATE PROCEDURE [xxx].[spciProcedurePerformanceStartRecord_help]
         (@ProcedureName VARCHAR(200),
          @ProcedureRecordCount INT = 1,
          @ProcedureStartDateTime DATETIME = GETDATE,
          @ProcedureLog_Note NVARCHAR(100),
          @ProcedureLogRowKey INT OUTPUT  --- I am passing this into my proc and 
    displaying it as output
         )
    AS
    BEGIN 
        -- Set Default return for @ProcedureLogRowKey, used if logging is not turned on. 
        SET @ProcedureLogRowKey = -1; 
    
        -- Check to see if performance logging is enabled 
        IF EXISTS(SELECT ROWID FROM LIVE.YPERCON 
                  WHERE YPROCNM_0 = @ProcedureName AND YLOGENA_0 = 2) 
        BEGIN
            INSERT INTO xxx.YPERLOG (YROWKEY_0, YPROCNM_0, YRECCNT_0, YSTRTDTT_0, YENDDTT_0, YLOGNOTE_0, 
                                     YDURMS_0, CREDATTIM_0, UPDDATTIM_0, AUUID_0, CREUSR_0, UPDUSR_0)   
                SELECT 
                    ISNULL(MAX(YROWKEY_0), 0) + 1, 
                    @ProcedureName, @ProcedureRecordCount, @ProcedureStartDateTime, 
                    '1753-01-01', 
                    @ProcedureLog_Note, 0, 
                    GETDATE(), GETDATE(), NEWID(), 'admin', 'admin'  
                FROM
                    xxx.YPERLOG
    
            SELECT @ProcedureLogRowKey = ISNULL(MAX(YROWKEY_0), 0) 
            FROM xxx.YPERLOG
        END 
        ELSE
        BEGIN
            DECLARE @Count integer
    
            SELECT @Count = COUNT(0) 
            FROM LIVE.YPERERR 
            WHERE YPROCNM_0 = @ProcedureName 
    
            IS ISNULL(@Count, 0) = 0
                INSERT INTO LIVE.YPERERR (YPROCNM_0, YREQDT_0, YLASTDT_0, YERRMSG_0, 
                                          CREDATTIM_0, UPDDATTIM_0, AUUID_0, CREUSR_0, UPDUSR_0)
                VALUES (@ProcedureName, GETDATE(), '1753-01-01', 'Controller not defined or active', 
                        GETDATE(), GETDATE(), NEWID(), 'admin', 'admin')
            ELSE 
                UPDATE xxx.YPERERR
                SET YLASTDT_0 = GETDATE()
                WHERE YPROCNM_0 = @ProcedureName
           END
    END 
    

    2 回复  |  直到 1 周前
        1
  •  1
  •   Marko Radivojević    1 周前

    问题在程序中 [xxx].[spciProcedurePerformanceStartRecord_help] 带参数 @ProcedureStartDateTime DATETIME . 您应该这样设置它的默认值:

    NULL

    @ProcedureStartDateTime DATETIME = NULL
    

    看起来像是

    CREATE PROCEDURE [xxx].[spciProcedurePerformanceStartRecord_help]
    (
    @ProcedureName VARCHAR(200)
    ,@ProcedureRecordCount INT = 1
    ,@ProcedureStartDateTime DATETIME = NULL
    ,@ProcedureLog_Note NVARCHAR(100)
    ,@ProcedureLogRowKey INT OUTPUT
    )
    AS
    BEGIN
    
        -- procedure's body
    
    END
    

    @ProcedureStartDateTime 参数值为 无效的 如果是,则将其值设置为 GETDATE()

    SET @ProcedureStartDateTime = ISNULL(@ProcedureStartDateTime, GETDATE())
    
        2
  •  0
  •   Venkataraman R    1 周前

    你已经宣布 DECLARE @ProcedureStartDateTime DATETIME 也没有给它设置任何值。因此,它有空值,您将空值传递给过程执行

    EXEC [XXX].[spciProcedurePerformanceStartRecord_help]
     '.[xxx].[zzzz_ERP_Cyyyyy]',
      1,
      @ProcedureStartDateTime, -- NULL value passed here
     'Contract Check',
     @ProcedureLogRowKey OUTPUT
    

    'YSTRTDTT_0', table '000.xxx.YPERLOG' ,不允许为空,您得到的是错误。