英文:
How to pass select/create query as parameter in snowflake stored procedure
问题
我正在尝试将参数传递给一个存储过程，该存储过程基本上是一个“选择/创建语句”。
我曾尝试使用:param和INDENTIFIER(:param)，但它不起作用。
错误：无效的标识符和绑定错误
代码：
CREATE OR REPLACE PROCEDURE TEST_PROC(query varchar)
returns varchar
LANGUAGE SQL
as
$$
begin
if(length(query)>=1 and IS_NOTNULL(query) )then
execute immediate (:query);
--execute immediate IDENTIFIER(:query);
end if;
end;
$$;
call TEST_PROC('CREATE OR REPLACE TABLE TBL1 AS SELECT * FROM TBL2')
英文:
I am trying to pass a parameter to a stored procedure which is basically 'select/create statement'.
I had used :param and INDENTIFIER(:param) but it is not working.
> Error : invalid identifier and binding error
Code:
CREATE OR REPLACE PROCEDURE TEST_PROC(query varchar)
returns varchar
LANGUAGE SQL
as
$$
begin
if(length(query)>=1 and IS_NOTNULL(query) )then
execute immediate (:query);
--execute immediate IDENTIFIER(:query);
end if;
end;
$$;
call TEST_PROC('CREATE OR REPLACE TABLE TBL1 AS SELECT * FROM TBL2')
答案1
得分: 0
If you change the "if" line to the following, then it should work:
如果将“if”行更改为以下内容，那么它应该可以正常工作：
if(length(:query)>=1 and :query IS NOT NULL ) then
The full code that I ran (which includes return statements to track what the SP is doing) is as follows:
我运行的完整代码（包括返回语句以跟踪存储过程的操作）如下：
CREATE OR REPLACE PROCEDURE TEST_PROC(query varchar)
returns varchar
LANGUAGE SQL
as
$$
begin
if(length(:query)>=1 and :query IS NOT NULL ) then
execute immediate (:query);
--execute immediate IDENTIFIER(:query);
else
RETURN 'No input provided';
end if;
return 'Query executed: ' || :query;
end;
$$
;
英文:
If you change the "if" line to the following then it should work:
if(length(:query)>=1 and :query IS NOT NULL ) then
The full code that I ran (which includes return statements to track what the SP is doing) is as follows:
CREATE OR REPLACE PROCEDURE TEST_PROC(query varchar)
returns varchar
LANGUAGE SQL
as
$$
begin
if(length(:query)>=1 and :query IS NOT NULL ) then
execute immediate (:query);
--execute immediate IDENTIFIER(:query);
else
RETURN 'No input provided';
end if;
return 'Query executed: ' || :query;
end;
$$
;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论