Lets say I have a stored procedure which has to be dynamically created
because I want to use an IN clause with a passed variable and/or want
to use a passed variable to get the 'top'. Is there any savings in
using a SP in this case over using a cfquery with proper
cfqueryparams? I'm going to test it but hearing from others who know
better is always good, especially when they can tell me that I'm wrong
in my approach.
The SP I'm using is below:
CREATE PROC gettopnews @top varchar(16), @jointypeid varchar(64),
@category varchar(256)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(1500)
SET @SQL =
'SELECT DISTINCT TOP ' + @top + ' newsid, pubdate
FROM news
WHERE (jointypeid IN (' + @jointypeid + '))
and (category = ' + @category + ')
ORDER BY pubdate DESC, newsid desc'
EXEC(@SQL)
END
GO
Thanks
--
Michael Dinowitz
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3333
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
