cfstoredproc or stored procedure problem

[n36z]

I am having a problem when calling a stored procedure. I have designed the stored procedure to accept "Null" values.

Condition 1
Configuation as ColumnA with a value of 1, a query is successfully returned with the expected data set
<cfprocparam cfsqltype="CF_SQL_INTEGER" value="1" type="In" null="No" dbvarname="@columnA">

Condition 2
When I use the following Proc Param configuration, no query is returned
<cfprocparam cfsqltype="CF_SQL_INTEGER" value="4" type="In" null="No" dbvarname="@columnB">

Data In Table
columnA columnB columnC
1 4 some data 1
2 5 some data 2
3 6 some data 3

<!--- The Stored Procedure --->
create procedure storedProc
(
@columnA int = NULL,
@columnB int = NULL
)

As

select
columnA,
columnB,
columnC

from table1

where 0 = 0

AND (columnA = @columnA OR @columnA IS NULL)
AND (columnB = @columnB OR @columnB IS NULL)

go

<!--- Call to the Stored Procedure from Cold Fusion --->
<cfstoredproc procedure="storedProc" datasource="datasource">

<cfprocresult name = "result">

<cfprocparam cfsqltype="CF_SQL_INTEGER" value="4" type="In" null="No" dbvarname="@columnB">

</cfstoredproc>

<cfdump var = "#result#">

 

[cfSearching]

<cfprocparam dbvarname="@columnB" ...>

CF does not support named parameters, just positional notation. You must to supply all parameters, in the proper order. To omit a value, use null=true.

Example

<cfstoredproc procedure="storedProc" datasource="datasource">
    <!--- 1st parameter is columnA --->
    <cfprocparam cfsqltype="CF_SQL_INTEGER" null="true" .... >
    <!--- 2nd parameter is columnB --->
    <cfprocparam cfsqltype="CF_SQL_INTEGER" value="4" ....>
    <cfprocresult name = "result">    
</cfstoredproc>

----------------------------------

http://cfsearching.blogspot.com/

 

[n36z]

Thanks