How to return the count of a table created and dropped within a SQL stored procedure in Snowflake?-CodePudding

I am trying to use a variable to store the count in a temporary table created within a stored procedure in Snowflake so that I can include the value in the return statement. When I try to do a select count(*) from the table I get SQL compilation error: Object 'CDP_SMS.DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM' does not exist or not authorized.. If I try to use LET to create a variable I get the same error. If I use SET to create a session variable, it doesn't immediately error but I am unable to access the session variable afterwards (I'd guess that session variables don't work in stored procedures). Removing the temporary keyword from the create table statement does not help.

However, I am able to use the table in an update statement (lines 36-48) and it works fine. Is there a way to store the count of a table created and dropped within a stored procedure to use in the return statement? I suppose I could use the logic that creates the temp table in a subquery and directly get the count but I'd really prefer not to do that (this code is a simplified version of the query that creates the temp table and it is actually pretty unwieldy with multiple unions and joins).

CREATE OR REPLACE PROCEDURE DOMAIN_CANONICAL.MFG_ITEM_LOAD_test(X_DAYS_BACK INTEGER)
returns string not null
language SQL
as
$$
BEGIN
LET TIMESTAMP_NOW TIMESTAMP := CURRENT_TIMESTAMP() ;

CREATE OR REPLACE TEMPORARY TABLE DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM (
      GHX_INTERNAL_ITEM_ID STRING,
      TRADEMARK_BRANDNAME STRING,
      DEVICE_PUBLISH_DATE STRING,
      CDP__ETL_INSERT_TIMESTAMP TIMESTAMP,
      CDP__ETL_UPDATE_TIMESTAMP TIMESTAMP,
      HASH_DELTA STRING
    )
 AS
 SELECT *, MD5(
             HASH(TRADEMARK_BRANDNAME) ||
             HASH(DEVICE_PUBLISH_DATE)) AS HASH_DELTA
 FROM(
    SELECT
      'GUDID'||DEVICE.PRIMARY_DI AS GHX_INTERNAL_ITEM_ID,
      DEVICE.BRAND_NAME AS TRADEMARK_BRANDNAME,
      DEVICE.DEVICE_PUBLISH_DATE AS DEVICE_PUBLISH_DATE,
      :TIMESTAMP_NOW AS CDP__ETL_INSERT_TIMESTAMP,
      :TIMESTAMP_NOW AS CDP__ETL_UPDATE_TIMESTAMP
    FROM BASE.GUDID_DEVICE DEVICE
    WHERE
        DEVICE.CDP__ETL_UPDATE_TIMESTAMP >= DATEADD(Day ,-1*:X_DAYS_BACK, CURRENT_DATE)
  );

  -- Successfully updates existing rows with changes using the temp table
  UPDATE DOMAIN_CANONICAL.MANUFACTURER_ITEM_temp MI
        SET
            MI.TRADEMARK_BRANDNAME = DMI.TRADEMARK_BRANDNAME,
            MI.DEVICE_PUBLISH_DATE = DMI.DEVICE_PUBLISH_DATE,
            CDP__ETL_UPDATE_TIMESTAMP = :TIMESTAMP_NOW
        FROM DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM DMI
          WHERE MI.GHX_INTERNAL_ITEM_ID IN (
        SELECT MI.GHX_INTERNAL_ITEM_ID FROM CDP_sms.DOMAIN_CANONICAL.MANUFACTURER_ITEM_temp MI
        INNER JOIN DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM DMI
          ON DMI.GHX_INTERNAL_ITEM_ID = MI.GHX_INTERNAL_ITEM_ID
        WHERE MI.HASH_DELTA != DMI.HASH_DELTA
            )
          AND MI.GHX_INTERNAL_ITEM_ID = DMI.GHX_INTERNAL_ITEM_ID;

      
      
//    let UPDATED_ROW_COUNT INTEGER := (select count(*) FROM DOMAIN_CANONICAL.MANUFACTURER_ITEM_temp MI 
//                                        INNER JOIN DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM DMI
//                                        ON DMI.GHX_INTERNAL_ITEM_ID = MI.GHX_INTERNAL_ITEM_ID
//                                        where MI.HASH_DELTA != DMI.HASH_DELTA); 
////    -- If Lines 52-55 are uncommented: SQL compilation error: Object 'CDP_SMS.DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM' does not exist or not authorized.
    
//   set UPDATED_ROW_COUNT = (select count(*) from DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM DMI); 
//   return $UPDATED_ROW_COUNT; 
//// If 58/59 are uncommented: -- SQL compilation error: error line 59 at position 10 Session variable '$UPDATED_ROW_COUNT' does not exist

//return (select count(*) from DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM);
//// If above line uncommented: -- SQL compilation error: Object 'CDP_SMS.DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM' does not exist or not authorized.

DROP TABLE IF EXISTS DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM;

let UPDATED_ROW_COUNT INTEGER := 100;
RETURN 'DOMAIN_CANONICAL.MANUFACTURER_ITEM_temp updated with ' || :UPDATED_ROW_COUNT || ' rows';
END;
$$

CodePudding user response：

Using INTO:

LET UPDATED_ROW_COUNT INT;

select count(*) 
INTO :UPDATED_ROW_COUNT 
FROM DOMAIN_CANONICAL.MANUFACTURER_ITEM_temp MI 
INNER JOIN DOMAIN_CANONICAL.TEMP_DELTA_MANUFACTURER_ITEM DMI
  ON DMI.GHX_INTERNAL_ITEM_ID = MI.GHX_INTERNAL_ITEM_ID
where MI.HASH_DELTA != DMI.HASH_DELTA;

CodePudding user response：

You can always use the global variable SQLROWCOUNT, for example:

CREATE OR REPLACE PROCEDURE SP_TEST()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE 
  row_cnt INT := 0;
BEGIN  
  CREATE OR REPLACE LOCAL TEMP TABLE tempTB
  AS
  SELECT SEQ4() 1 AS val, 100 AS a
    FROM TABLE(GENERATOR(ROWCOUNT => 1000)) AS t;
    
  UPDATE tempTB
     SET a = 200
   WHERE val BETWEEN 301 AND 800;
   
  row_cnt := IFNULL(SQLROWCOUNT, 0)::int;
  
  DROP TABLE IF EXISTS tempTB;
  
  RETURN 'Rows Affected: ' || row_cnt;
END;
$$;

CALL SP_TEST();

Result:

Rows Affected: 500