英文:
Using binding inside SQL procedures
问题
我在尝试让以下代码工作时遇到了问题：
create or replace secure procedure create_wh (wh_name varchar)
returns varchar
language sql
comment = '<string_literal>'
execute as owner
as
begin
create warehouse if not exists :wh_name
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true
initially_suspended = true;
return 'SUCCES';
end;
这个存储过程的思想是可以使用仓库的名称来调用它。在尝试运行上面的代码时，在create warehouse语句之后出现了unexpected 'if'错误。
我猜测问题可能与将参数绑定到查询有关，但我无法弄清楚是什么。
英文:
I am having trouble getting the following code to work:
create or replace secure procedure create_wh (wh_name varchar)
returns varchar
language sql
comment = '<string_literal>'
execute as owner
as
begin
create warehouse if not exists :wh_name
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true
initially_suspended = true;
return 'SUCCES';
end;
The idea is that the SP can be called with a name for a warehouse. It errors in unexpected 'if' after the create warehouse statement when trying to run the above code.
I am guessing I am missing something in relation to binding the param to the query, but I can't figure out what.
答案1
得分: 1
可以使用IDENTIFIER(:wh_name)将仓库名称作为参数传递：
create or replace secure procedure create_wh (wh_name varchar)
returns varchar
language sql
comment = '<string_literal>'
execute as owner
as
begin
create warehouse if not exists IDENTIFIER(:wh_name)
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true
initially_suspended = true;
return 'SUCCES';
end;
CALL create_wh('test');
SHOW WAREHOUSES;
英文:
It is possible to provide warehouse name as parameter by using IDENTIFIER(:wh_name):
create or replace secure procedure create_wh (wh_name varchar)
returns varchar
language sql
comment = '<string_literal>'
execute as owner
as
begin
create warehouse if not exists IDENTIFIER(:wh_name)
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true
initially_suspended = true;
return 'SUCCES';
end;
CALL create_wh('test');
SHOW WAREHOUSES;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论