Run the SP in Query Analyser to see the resultsets you're getting back. It'll be tow or three. Chances are, the third SELECT isn't being run and QA should show this.
Does the SP return the first two resultsets? If you leave your SP as it is you could do a check for getNavSubElements with IsDefined(). Adrian -----Original Message----- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: 23 October 2005 05:04 To: CF-Talk Subject: Re: cfprocresult and recordcount 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:221965 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
