OK, the issue might be with my SP; I tried to dump the query structure
and got an error. I'm pushing my personal stored procedure envelope
here so might have made a mistake.
Anyway, here's my cfstoredproc call:
<cfstoredproc procedure="spGetNav" datasource="#request.app.dsname#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@cid"
value="#URL.cid#" null="#isNullNumeric(URL.cid)#">
<cfprocresult name="getAllNavElements" resultset="1">
<cfprocresult name="getNavElements" resultset="2">
<cfprocresult name="getNavSubElements" resultset="3">
</cfstoredproc>
And here is the stored procedure:
CREATE PROCEDURE spGetNav
@cid integer
AS
SELECT C.ID, C.parentid, C.displayorder, C.title, C.redirect
FROM tblContent C
WHERE C.active = 1
SELECT C.ID, C.title, C.redirect
FROM tblContent C
WHERE C.parentid IS NULL
ORDER BY C.displayorder
DECLARE @parentidcount integer
SET @parentidcount = (SELECT count(C.parentid) AS
parentidcount
FROM tblContent C
WHERE C.ID = @cid)
IF (@parentidcount > 0)
BEGIN
SELECT ID, title, redirect
FROM getAllNavElements
WHERE parentid = (SELECT
C.parentid
FROM
tblContent C
WHERE
C.ID = @cid)
ORDER BY displayorder
END
GO
As I mentioned, I'm just beginning to go beyond the basics as far as
stored procedures go, so my guess is that I've done something wrong.
The first two SELECT statements are returning result sets. The third
portion of the SP (or so I thought) begins with the DECLARE statement,
and my guess is that's what is causing the problem.
Can someone point me in the right direction on this one?
Thanks,
Pete
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:221963
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
