Hooray for Neil! This has just saved me a whole lotta pain.
Thanks! On 8/12/05, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > Here is how you can do it using a List To Table scenario .. > > First off - run this UDF into your DB > ====================== > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS OFF > GO > > /* > Author: Neil Robertson-Ravo > File: listToTable > Date Created: 11/04/2004 > Purpose: This function takes a list and converts it > to a two-column > table (position and value) > Input: @list - List to be converted > @delim - List Delimiter > */ > > ALTER FUNCTION listToTable(@list as varchar(8000), @delim as varchar(10)) > RETURNS @listTable table( > Position int, > Value varchar(8000) > ) > AS > BEGIN > declare @myPos int > set @myPos = 1 > > while charindex(@delim, @list) > 0 > begin > insert into @listTable(Position, Value) > values(@myPos, left(@list, charindex(@delim, @list) - 1)) > > set @myPos = @myPos + 1 > if charindex(@delim, @list) = len(@list) > insert into @listTable(Position, Value) > values(@myPos, '') > set @list = right(@list, len(@list) - charindex(@delim, > @list)) > end > > if len(@list) > 0 > insert into @listTable(Position, Value) > values(@myPos, @list) > > RETURN > END > > > GO > SET QUOTED_IDENTIFIER OFF > GO > SET ANSI_NULLS ON > GO > > > Secondly, you need your SP. > ================== > > CREATE PROCEDURE usp_GetNewsContent @vcContentType varchar(8000) > AS > > SELECT * > FROM Content > INNER JOIN Links > ON Content .ContentID = Links.ContentID > WHERE (Content.ContentType IN ( > Select value from dbo.listToTable(vcContentType, ',') > )) > > This should serve you well ;-) No dynamic SQL in sight! Wahoo and it's > reusable ;-)! > > > > > > This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, > Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, > Registered in England, Number 678540. It contains information which is > confidential and may also be privileged. It is for the exclusive use of > the > intended recipient(s). If you are not the intended recipient(s) please > note > that any form of distribution, copying or use of this communication or the > information in it is strictly prohibited and may be unlawful. If you have > received this communication in error please return it to the sender or > call > our switchboard on +44 (0) 20 89107910. The opinions expressed within this > communication are not necessarily those expressed by Reed Exhibitions. > Visit our website at http://www.reedexpo.com > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase Dreamweaver with Homesite Plus from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=54 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2392 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
