How can you use a variable declared in a Snowflake SQL stored procedure as an argument for another s-CodePudding

Hello I am working with Snowflake and am creating a SQL stored procedure (CDS_EXTRACT). Within CDS_EXTRACT, I have to execute other stored procedures (CDS_EXTRACT_NEW_LOG_ENTRY). The arguments for CDS_EXTRACT_NEW_LOG_ENTRY need to come from variables I declared CDS_EXTRACT. Does anyone know how I can reference variables defined in CDS_EXTRACT to use as arguments as CDS_EXTRACT_NEW_LOG_ENTRY?

In particular, I would like to use the JOBNAME, SOURCEQUERY, CDCQUERY, CDCVALUE variables I declared in CDS_EXTRACT as arguments when I call CDS_NEW_LOG_ENTRY.

Running procedure B outside of procedure A works fine btw.

Hopefully that made sense please refer to my code for better understanding

CREATE OR REPLACE PROCEDURE CDS_EXTRACT (CDSID number)
RETURNS varchar(1677216)
LANGUAGE SQL
AS
    DECLARE 
        JOBNAME varchar(255);
        SOURCEQUERY VARCHAR(16777216);
        CDCQUERY VARCHAR(16777216);
        CDCVALUE VARCHAR(100) DEFAULT NULL;
        STAGE_DATABASE VARCHAR(100);
        STAGE_SCHEMA VARCHAR(100);
        STAGE_TABLE VARCHAR(100);
        FILE_FORMAT VARCHAR(100);
        BLOB_DIRECTORY VARCHAR(100);
        BLOB_FILE VARCHAR(100);
        SINGLE_FILE_FLAG BOOLEAN;
        OVERWRITE_FILE BOOLEAN;
        MAX_FILESIZE int DEFAULT 4900000000;
        START_DATE TIMESTAMP_NTZ(9);
        LAST_RUN_DATE TIMESTAMP_NTZ(9);
        LAST_RUN_CYCLE VARCHAR(30);
        LAST_RUN_COMPLETE BOOLEAN;
        RUNNEXT BOOLEAN;
        ERROR_FLAG BOOLEAN;
    BEGIN
        -- Gathering job info
        JOBNAME           := (SELECT JOBNAME FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        SOURCEQUERY       := (SELECT SOURCEQUERY FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        CDCQUERY          := (SELECT CDCQUERY FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        CDCVALUE          := (SELECT CDCVALUE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        STAGE_DATABASE    := (SELECT STAGE_DATABASE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        STAGE_SCHEMA      := (SELECT STAGE_SCHEMA FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        STAGE_TABLE       := (SELECT STAGE_TABLE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        FILE_FORMAT       := (SELECT FILE_FORMAT FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        BLOB_DIRECTORY    := (SELECT BLOB_DIRECTORY FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        BLOB_FILE         := (SELECT BLOB_FILE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        SINGLE_FILE_FLAG  := (SELECT SINGLE_FILE_FLAG FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        OVERWRITE_FILE    := (SELECT OVERWRITE_FILE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        MAX_FILESIZE      := (SELECT MAX_FILESIZE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        START_DATE        := (SELECT START_DATE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        LAST_RUN_DATE     := (SELECT LAST_RUN_DATE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        LAST_RUN_CYCLE    := (SELECT LAST_RUN_CYCLE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        LAST_RUN_COMPLETE := (SELECT LAST_RUN_COMPLETE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        RUNNEXT           := (SELECT RUNNEXT FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        ERROR_FLAG        := (SELECT ERROR_FLAG FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        
        -- Update Start date, and last run date (need to add way to update run cycle as well)
        CALL CDS_EXTRACT_UPDATE_RUNCYCLE_DATE(:CDSID);
        
        -- Insert new log entry row
        CALL CDS_EXTRACT_NEW_LOG_ENTRY(00000, :CDSID, JOBNAME, SOURCEQUERY, CDCQUERY, CDCVALUE);
        
        RETURN 'Procedure completed!';
    END
;

Running results in the following error: Error:'STATEMENT_ERROR' on line 47 at position 8 : SQL compilation error: error line 1 at position 60 (line 99)

CodePudding user response：

The error message is related to calling the CDS_EXTRACT_NEW_LOG_ENTRY procedure, but you didn't share the whole error message!?

I tried to reproduce the issue, and the closest message I get is this:

Uncaught exception of type 'STATEMENT_ERROR' on line 47 at position 8 : SQL compilation error: error line 1 at position 60 invalid identifier 'JOBNAME'

I put colon symbol (:) before the variables and it worked:

CALL CDS_EXTRACT_NEW_LOG_ENTRY(00000, :CDSID, :JOBNAME, :SOURCEQUERY, :CDCQUERY, :CDCVALUE);

CodePudding user response：

Each variable use in SQL context should be prepended with ::

CALL CDS_EXTRACT_NEW_LOG_ENTRY(00000, :CDSID, :JOBNAME, :SOURCEQUERY, :CDCQUERY, :CDCVALUE);

SELECT INTO is much cleaner way of assigning multiple variables originated from the same query:

SELECT JOBNAME, SOURCEQUERY,  CDCQUERY, CDCVALUE, ...       
INTO :JOBNAME, :SOURCEQUERY, :CDCQUERY, :CDCVALUE, ...                               
FROM CDS.TALEND_CDS_BLOBEXTRACT 
WHERE CDSID = :CDSID;