问题

我尝试在Snowflake中运行以下存储过程，但我遇到了绑定变量错误：

```sql
CREATE OR REPLACE PROCEDURE sp_name(TASKNUM FLOAT)
RETURNS STRING
LANGUAGE SQL

AS
$$
DECLARE
  sqlcom STRING;
 
BEGIN
  
  sqlcom := 'INSERT INTO task_tracker VALUES (''TASK_SP_CALL'' || :TASKNUM, CURRENT_TIMESTAMP(), ''Task Done - TASK_SP_CALL Remarks'', ''Active'')';
  EXECUTE IMMEDIATE sqlcom;
  
  RETURN 'Succeeded';
  
EXCEPTION
    WHEN OTHER THEN
    RETURN 'Failed: ' || SQLERRM;
END;

$$;

当我执行这个：

call sp_name(2)

我得到：

SQL 编译错误
未设置绑定变量 :task_num。
当我在存储过程内部硬编码数值时，它可以工作。

<details>
<summary>英文:</summary>

I try to run in Snowflake the below stored procedure but I get the bind variable error:

CREATE OR REPLACE PROCEDURE sp_name(TASKNUM FLOAT)
RETURNS STRING
LANGUAGE SQL

AS
$$
DECLARE
sqlcom STRING;

BEGIN

sqlcom := 'INSERT INTO task_tracker VALUES (''TASK_SP_CALL'' || :TASKNUM, CURRENT_TIMESTAMP(), ''Task Done - TASK_SP_CALL Remarks'', ''Active'')';
EXECUTE IMMEDIATE sqlcom;

RETURN 'Succeeded';

EXCEPTION
WHEN OTHER THEN
RETURN 'Failed: ' || SQLERRM;
END;

$$;

When I execute this:  

    call sp_name(2)

I get: 

> SQL compilation error    
> Bind variable :task_num not set.

When I hard code the value inside the stored procedure, it works.

</details>


# 答案1
**得分**: 2

动态SQL字符串应该使用参数占位符 - `?` 进行参数化，并使用 `EXECUTE IMMEDIATE sql USING (var1)` 调用：

```sql
CREATE OR REPLACE PROCEDURE sp_name(TASKNUM FLOAT)
RETURNS STRING
LANGUAGE SQL
AS
DECLARE
  sqlcom STRING;
BEGIN
  sqlcom := 'INSERT INTO task_tracker VALUES (''TASK_SP_CALL'' || ?,
                                              CURRENT_TIMESTAMP(),
                                              ''Task Done - TASK_SP_CALL Remarks'',
                                              ''Active'')';
  EXECUTE IMMEDIATE sqlcom USING (TASKNUM);
  
  RETURN 'Succeeded';  
EXCEPTION
    WHEN OTHER THEN
    RETURN 'Failed: ' || SQLERRM;
END;

CALL sp_name(1);
-- Succeeded

注意：

1. "Blind insert" 是一种反模式，应提供明确的列列表 - INSERT INTO task_tracker(col1, col2, ...)
2. INSERT INTO ... VALUES (...) 仅允许常量或简单表达式，如果使用更复杂的函数，则应考虑使用 INSERT INTO ... SELECT ...。

CREATE OR REPLACE PROCEDURE sp_name(TASKNUM FLOAT)
RETURNS STRING
LANGUAGE SQL
AS
DECLARE
  sqlcom STRING;
BEGIN
  sqlcom := 'INSERT INTO task_tracker VALUES (''TASK_SP_CALL'' || ?,
                                              CURRENT_TIMESTAMP(),
                                              ''Task Done - TASK_SP_CALL Remarks'',
                                              ''Active'')';
  EXECUTE IMMEDIATE sqlcom USING (TASKNUM);
  
  RETURN 'Succeeded';  
EXCEPTION
    WHEN OTHER THEN
    RETURN 'Failed: ' || SQLERRM;
END;

CALL sp_name(1);
-- Succeeded