hmmm, This might work....(I havent tested it or parsed it as I just did it in my mail client...)
DECLARE @vcContentType VARCHAR(8000) SELECT * FROM Content INNER JOIN Links ON Content .ContentID = Links.ContentID WHERE (Content.ContentType IS NULL OR (Content.ContentType IN ( Select value from dbo.listToTable(@vcContentType, ',') ))) You could of course perform some dynamic SQL, but that would just not be on ;-) N Rick Melnyck wrote: >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:2399 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
