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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2346
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=11502.10531.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
