dynamic top clause will only work with sql 2005 & above. yuo should be able to reset the ROWCOUNT var immediately after the query.
----- Original Message ----- From: "Michael Dinowitz" <[email protected]> To: "sql" <[email protected]> Sent: Thursday, April 29, 2010 12:54 AM Subject: Re: savings with dynamic stored procedure? > > Thanks. I'm going to try that out. A followup question has to do with > how you deal with the TOP clause below. You're putting the passed @top > value directly into the query within parenthesis. I tried that out and > it didn't work. Is this right and does it work on SQL2k? > > As an alternative, can I use the set rowcount method? I assume so but > my only question would be if the setting and resetting of the rowcount > will effect queries outside of the SP that it's used in. If another > query is run after the first rowcount is set and before the second, > will it be limited to the rowcount that is set to @top? > > SET ROWCOUNT @top > SELECT DISTINCT newsid, pubdate > FROM news > WHERE (jointypeid IN (SELECT value FROM dbo.fn_Split(@jointypeid,','))) > and (category = @category ) > ORDER BY pubdate DESC, newsid desc > SET ROWCOUNT 0 > > > >> SELECT DISTINCT TOP (@top) newsid, pubdate >> FROM news >> WHERE (jointypeid IN (SELECT value FROM dbo.fn_Split(@jointypeid,','))) >> and (category = @category ) >> ORDER BY pubdate DESC, newsid desc > > Thanks again > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3338 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
