page98
80
The importance of bind variables within queries is often overlooked, particularly by
more junior programmers. Bind variables enable query reuse requiring a hard parse only
for the first time they are seen by the query optimizer; all subsequent query submissions
will reuse the original execution plan.
In contrast, while the fabric of a SQL statement may remain static, without using
bind variables, the literals embedded will always force a hard-parse. As subsequent
query submissions are seen as new statements, query reuse cannot occur.
Best practice is to implement bind variables where queries are to be reused. The
small overhead in development cost is always returned several times in lower execution
costs. Furthermore, encapsulation of reusable queries using bind variables within stored
procedures is a must-have to reduce both complexity and development cost.
To illustrate the use of bind variables, you will create a JavaScript stored procedure
called
sp_test_bind
. Note that SQL scripting can also be used.
Within
sp_test_bind
, two statements implement bind variables.
The first statement
sql_stmt = "SELECT :1"
provides a placeholder for the bind
variable :1.
The second statement
stmt = snowflake.createStatement( { sqlText: sql_
stmt, binds:[P_NAME] } );
declares the bind variable to replace :1 at runtime.
Note in JavaScript the bind variable name must be in UPPERCASE to reference
parameters passed into the stored procedure.
CREATE OR REPLACE PROCEDURE sp_test_bind( P_NAME STRING )
RETURNS string
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$
var stmt = "";
var sql_stmt = "";
var err_state = "";
var retval = "";
var result = "";
sql_stmt = "SELECT :1"
stmt = snowflake.createStatement( { sqlText: sql_stmt, binds:[P_
NAME] } );
ChapTer 3
The Query profiler