you do not need dynamic query for this. You can create a sql table-valued
function that will take a string containing comma delimited values and
return a table variable.
Then you can use this function in your query. See eg below:
CREATE FUNCTION [dbo].[fn_Split](@text VARCHAR(MAX), @delimiter VARCHAR(5) =
',')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value VARCHAR(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
END
SET @text = RIGHT(@text, (LEN(@text) - (@index+LEN(@delimiter)-1)))
END
RETURN
END
GO
CREATE PROC gettopnews @top varchar(16), @jointypeid varchar(64),
@category varchar(256)
AS
BEGIN
SET NOCOUNT ON
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
END
GO
Thanks
DK
www.daksatech.com
----- Original Message -----
From: "Michael Dinowitz" <[email protected]>
To: "sql" <[email protected]>
Sent: Wednesday, April 28, 2010 9:16 PM
Subject: savings with dynamic stored procedure?
>
> 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:3334
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
