CODEWITHSUNDEEP
sqlsql-server-2012
user3798811
user3798811

Reputation: 523

Passing Variable from stored procedure to another

I have a stored procedure that I need pass the parameter from one to the other procedure and have it display as an output. I am declaring the following in the header of my procedure [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

I am getting the following error:

Msg 515, Level 16, State 2, Procedure spciProcedurePerformanceStartRecord_help, Line 33 [Batch Start Line 17]
Cannot insert the value NULL into column 'YSTRTDTT_0', table '000.xxx.YPERLOG'; column does not allow nulls. INSERT fails.

Here is the procedure that I am getting the variable from to pass into my procedure [xxx].[zzzz_ERP_Cyyyyy]

 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

Thanks in advance.

Upvotes: 0

Views: 90

Answers (2)

Marko Radivojević
Marko Radivojević

Reputation: 448

The issue is in procedure [xxx].[spciProcedurePerformanceStartRecord_help] with parameter @ProcedureStartDateTime DATETIME. You should set its default value this way:

In declaration set default value as NULL

@ProcedureStartDateTime DATETIME = NULL

It would look like tihs

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

Inside procedure, at the beginning, check if @ProcedureStartDateTime parameter's value is NULL and if it is, set its value to GETDATE().

SET @ProcedureStartDateTime = ISNULL(@ProcedureStartDateTime, GETDATE())

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 13009

You have declared DECLARE @ProcedureStartDateTime DATETIME and did not set any value to it. so, it is having NULL value and you are passing NULL value to the procedure execution

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

As the target column 'YSTRTDTT_0', table '000.xxx.YPERLOG', does not allow NULLs, you are getting error.

Upvotes: 1

Related Questions