Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cfstoredproc or stored procedure problem

Status
Not open for further replies.

n36z

Programmer
Jul 26, 2011
13
US
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#">
 
<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
Code:
<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>

----------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top