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#">
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#">