Cfstoredprocedure not returning data

dev support
1

Hi,

StoredProcedure when executed using <cfstoredproc> tag is not returning data. But the same executed using <cfquery> is returning data.

Sample code :

<cfstoredproc procedure="spTeamHours" datasource="teamDSN" result="qProcResult" debug="true" returncode="true" >
		<cfprocparam cfsqltype="cf_sql_integer" type="in" value=5 dbvarname="@PI_TeamID">
		<cfprocresult name="local.hoursResult" resultset=1 >
</cfstoredproc>

is returning 0 records

<cfquery datasource="teamDSN" name="results" debug="yes">
    exec spTeamHours @PI_TeamID=5;
</cfquery>

is returning records.

Issue in Lucee 5.1.0
Database MSSQL.

Anyone came across this issue ?

Thanks,
Saravanan K

2

@Saravana_K I’ve analysed and tried to reproduce this issue on lucee 5.1.0.34 and 5.1.0.9, with a simple stored procedure using MS Sql 2012.

But I couldn’t get the issue. Please provide some more details like exact version of lucee you having, sample code for stored procedure. My tested stored procedure and cfm code is following:

Stored Proc

Create  PROCEDURE GetTitles(@id INT)
AS
BEGIN
SELECT title FROM Posts WHERE id=@id
END

CFML Test Case

<cfstoredproc procedure="GetTitles" datasource="test" result="qProcResult" debug="true" returncode="true" >
	<cfprocparam cfsqltype="cf_sql_integer" type="in" value=5 dbvarname="@id">
	<cfprocresult name="local.hoursResult" resultset=1 >
</cfstoredproc>
<cfdump var="#qProcResult#" />
<cfdump var="#local.hoursResult#" />

<cfquery datasource="test" name="results" debug="yes">
    exec GetTitles @id=5;
</cfquery>
<cfdump var="#results#" />