How to handle Exception In SQL Procedure returning Resultset as table

Question

I am writing Snowflake Procedure and using SNOWSCRIPT SQL here is sample code, what i am doing .I am passing SQL query as Input parameter to this procedure and trying to return the table as resultset object, I am trying to handle sql query exception in this code block using exception handling . I am getting following error "SQL compilation error: error line 36 at position 4 Declared return type 'TABLE' is incompatible with actual return type 'SCALAR'"

I want to Handle error and return the handled error to caller of procedure.

My question is how do i handle this exception.

 CREATE OR REPLACE PROCEDURE SP_TEST_RESULTTEST_ERROR_AHANDING( pSelectSql VARCHAR(1000))
    
    RETURNS TABLE()
    
    LANGUAGE SQL
    
    AS
    
    $$
    
    BEGIN
    
    DECLARE
    
        
                                                                            
    
       SELECT_EXCEPTION  EXCEPTION (-20002, 'Select Query Exception Occured');   
      
                                                                              
        SelectQueryResultSet RESULTSET;
        
       Query_Select_Statement VARCHAR;                                                              
      
    BEGIN
    
      
      
      Query_Select_Statement:=pSelectSql;
      
      SelectQueryResultSet := (EXECUTE IMMEDIATE :Query_Select_Statement);
      
      RETURN TABLE(SelectQueryResultSet);
      
    END;
    
                                                                              
    EXCEPTION 
    
    
          
       
       WHEN STATEMENT_ERROR then
        RETURN OBJECT_CONSTRUCT('Error type', 'STATEMENT_ERROR',
                                'SQLCODE', sqlcode,
                                'SQLERRM', sqlerrm,
                                'SQLSTATE', sqlstate);  
    
    END;               

             

$$ 

Answer 1

You cannot return tables from Snowflake stored procedures, what you can do is append the rows in with the delimited strings or variants with JSON(the max limit will be 16mb. You can write a function which will return a Table data type, but a function in Snowflake cannot use Dynamic SQL. when you have a Table as a return type then we need to specify the column and the data type. Please find the example as follows.

create or replace function testing_func()
RETURNS TABLE(x number,y number)
language SQL
AS $$
  SELECT 1,2 UNION ALL
  SELECT 3,4 

$$;
select * from table(testing_func());