If/then -----Original Message----- From: Rick Melnyck [mailto:[EMAIL PROTECTED] Sent: Friday, October 14, 2005 2:40 PM To: SQL Subject: Re: Using IN clause with a stored procedure
Thanks for the great post. The question I have is what happens if I pass a null parameter to the following select? SELECT * FROM Content INNER JOIN Links ON Content .ContentID = Links.ContentID WHERE Content.ContentType IN ( Select value from dbo.listToTable(@vcContentType, ',') ) I am using something similar and running into the following problem, if I pass a parameter it works fine but if i pass null it does not. What I want is for it to return everything if the parameter is null. Any ideas? >4th time lucky... > > >SELECT * >FROM Content >INNER JOIN Links >ON Content .ContentID = Links.ContentID >WHERE Content.ContentType IN ( > Select value from dbo.listToTable(@vcContentType, ',') >) > > >LOL!!! > > > >-----Original Message----- >From: Robertson-Ravo, Neil (RX) >[mailto:[EMAIL PROTECTED] >Sent: 11 August 2005 16:15 >To: SQL >Subject: RE: Using IN clause with a stored procedure > >Uurgh, sorry it 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:10 >To: SQL >Subject: RE: Using IN clause with a stored procedure > >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 >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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2398 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
