CODEWITHSUNDEEP
sqlstored-proceduressnowflake-cloud-data-platformdatabase-cursor
Naveen Srikanth
Naveen Srikanth

Reputation: 789

snowflake procedure syntax error while querying information schema

Idea is I am trying to query information schema metadata and build columns based on datatypes if date then I create min date max date ,if number then check count of distinct or count rows

I have simplified now I will pass dbname , schema name and table name as parameters,

My output should return with below columns SCHEMA_NM,TBL_NAME,_COUNT,_DISTINCT_COUNT,_MIN_DATE,_MAX_DATE.

For testing purpose at least if two measure is syntactically correct remaining I will take care

CREATE OR REPLACE PROCEDURE PROFILING( DB_NAME VARCHAR(16777216),TBL_SCHEMA VARCHAR(16777216), TBL_NAME VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS 
$$
DECLARE
BEGIN
    create or replace temporary table tbl_name as (select case when data_type=NUMBER then (execute immediate 'select count(col_val) from ' || :1 || '.' ||  :2 || '.' ||  :3) else null end as col_value_num,case when data_type=DATE then (execute immediate 'select count(col_val) from ' || :1 || '.' ||  :2 || '.' ||  :3) else null end as col_min_date from   ':1.information_schema.columns where table_catalog=:1 and table_schema=:2 and table_name=:3 ' )) ;
     insert into some_base_table as select * from tbl_name;
     truncate tbl_name 
RETURN 'SUCCESS';
END;
$$;

With above query I am getting below error

 error : SQL compilation error: Invalid expression value (?SqlExecuteImmediateDynamic?) for assignment.

Any help here please

Upvotes: 0

Views: 483

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

There are multiple errors in your code, but the error you mentioned is about this line:

num := (execute immediate "select count(col_val) from tab_cat.tab_schema.tab_name") ;

NUM is declared as integer, you can't directly assign a value from execute immediate. Execute immediate statement returns a resultset. To access the value you need to define a cursor and fetch the data.

The SQL should be surrounded by single quotes (not double quotes), and you should also build the string correctly. Something like this:

result := (execute immediate 'select count(col_val) from ' || tab_cat || '.' ||  tab_schema || '.' ||  tab_name ) ;

Upvotes: 1

Related Questions