What is a bound variable?

Question

There is a cffunction (in a cfc document) which authenticates a user. It references a stored procedure and has a cfprocparam which is of type "out". On the Adobe CFML reference it says that means that "the parameter is used to receive data from the database system only. Passes the parameter as a bound variable."

What is a bound variable?

Answer 1

From wikipedia:

(computing) A variable that is associated with a value, and therefore a variable that has an allocated storage location. If the programming language implements a representation of a canonical unknown value, infinity, etc., the value bound to the variable may be one of these.

Notice the bold portion

Variables that are not bound to a location are known as free variables.

Answer 2

The term "bound variable" may be a bad choice of words from Adobe. I think they mean it is bound to the variable inside the stored proc, not a bind variable in the "save the explain plan" sense (that should be true of both in variables and out variables). There are some good reasons to choose "out" over "in" and there are also side effects that you may find useful or may burn you if you don't expect them.

First off, if a parameter in a stored procedure is type "in" then most databases will not let you assign to it in the proc - very helpful when you know a variable should not be changed. Here is an Oracle example...

 CREATE PROCEDURE bind_test(p_testin IN NUMBER, p_testout OUT NUMBER) IS
 BEGIN

 -- p_testin  := 100; -- error can not be used as an assignment target
 p_testout := 100;

 END bind_test;

p_testin can't be changed, only used in places like "where" clauses or to initiate other variables but p_testout can be changed.

Second, you can take this one step farther by using the cfprocparam "variable =" syntax in cold fusion and actually get back the changed value in ColdFusion without going through a cfprocresult. Notice in this example the initial values for nTypeIn and nTypeOut both start at 10...

 <cfset nTypeIn  = 10 >
 <cfset nTypeOut = 10 >

 <cfstoredproc  procedure = "bind_test" dataSource = "#ProdDB#" returnCode = "No">
    <cfprocparam type="in"  cfsqltype="CF_SQL_INTEGER" variable="nTypeIn"  value="#nTypeIn#"  null="No"> 
    <cfprocparam type="out" cfsqltype="CF_SQL_INTEGER" variable="nTypeOut" value="#nTypeOut#" null="No"> 
 </cfstoredproc>

 <cfdump var="#nTypeIn#">
 <cfdump var="#nTypeOut#">

In Oracle at the end of this test nTypeIn will be 10 but nTypeOut will be 100 not 10. That can be very useful as long as you are expecting the database stored proc to change it.

Hope that sheds a bit more light on the question.