英文:
call stored procedure block in an IF block in Snowflake
问题
我试图将一个CALL块放入一个IF块中，如下所示：
if (CALL called_proc(:upper_name) = 'Error') THEN
RAISE :proc_exception;
END IF;
独立的过程'called_proc'将接收:upper_name变量并检查它是否包含分号，如下所示：
IF ((SELECT CONTAINS (:proc_variable, ';'))=TRUE) THEN
RETURN 'Error';
END IF;
如果有分号，它将返回'Error'，然后IF块将引发proc_exception。
上面的IF块显然是错误的，所以我想知道在IF块中正确使用CALL存储过程块的方法。
提前感谢您！
英文:
I'm trying to put a CALL block into an IF block like so:
if (CALL called_proc(:upper_name) = 'Error') THEN
RAISE :proc_exception;
END IF;
The separate procedure 'called_proc' will be passed the :upper_name variable and will check whether it's got a semi colon or not like so:
IF ((SELECT CONTAINS (:proc_variable, ';'))=TRUE) THEN
RETURN 'Error';
END IF;
If it has a semi colon, it will return 'Error' then the IF block will raise the proc_exception.
The if block above is obviously wrong so I was wondering what's the right way to have a CALL stored proc block inside an IF block.
Thanks in advance!
答案1
得分: 1
You can do it in this way:
创建或替换过程 called_proc(upper_name varchar(100))
返回 varchar
语言 SQL
作为
$$
BEGIN
IF ((SELECT CONTAINS (:upper_name, '';''))=TRUE) THEN
RETURN ''Error'';
END IF;
RETURN ''Success'';
END;
$$;
When I call this Stored Procedure:
调用 called_proc('test;1');
I get:
CALLED_PROC
Error
Then I used this stored procedure to do what you wanted:
DECLARE
my_exception EXCEPTION (-20002, ''Raised MY_EXCEPTION.'');
msg varchar;
BEGIN
CALL called_proc('test;1') INTO :msg;
IF (msg = ''Error'') THEN
RAISE my_exception;
END IF;
END;
This gives me:
Of course you can add this block into another stored procedure if you want.
英文:
You can do it in this way:
create or replace procedure called_proc(upper_name varchar(100))
returns varchar
language SQL
as
$$
BEGIN
IF ((SELECT CONTAINS (:upper_name, ';'))=TRUE) THEN
RETURN 'Error';
END IF;
RETURN 'Success';
END;
$$;
When I call this Stored Procedure:
call called_proc('test;1');
I get:
CALLED_PROC
Error
Then I used this stored procedure to do what you wanted:
DECLARE
my_exception EXCEPTION (-20002, 'Raised MY_EXCEPTION.');
msg varchar;
BEGIN
CALL called_proc('test;1') INTO :msg;
IF (msg = 'Error') THEN
RAISE my_exception;
END IF;
END;
This gives me:
Of course you can add this block into another stored procedure if you want.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论