RE: Using IN clause with a stored procedure

[Robertson-Ravo, Neil (RX)]

Sorry the SP should be

SELECT *
FROM Content
INNER JOIN Links
ON Content .ContentID = Links.ContentID
WHERE (Content.ContentType IN (
Select value from dbo.listToTable(@vcContentType, ',')
))





-----Original Message-----
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED] 
Sent: 11 August 2005 16:08
To: SQL
Subject: RE: Using IN clause with a stored procedure

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



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Protect Your PC from viruses, hackers, spam and more. Buy PC-cillin with Easy 
Installation & Support 
http://www.houseoffusion.com/banners/view.cfm?bannerid=61

Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2347
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