Where clause using a list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where clause using a list

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-27 : 15:23:40

Hi all,

I am working on a Sproc that is being passed a list (comma seperated) variable, from an HTML select box.

For example the list could contain :
10,20,30

I do have a function from that I use to split the list as follows :


Create  FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
	Return
END

I have used it before successfully. But cannot get it to work in where clause.

What I would like to do is something like this :


WHERE [DATA] FROM DBO.SPLIT(@Category, ',')) = dbo.SpecialCategory.Category

Not working for me obviously. Question is should I be able to make this work or is there a better tool that I am not aware of in advance?

Thanks in advance,
Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 15:32:10

SELECT * FROM dbo.SpecialCategory
INNER JOIN dbo.Split(@Category, ',') AS x ON x.Data = dbo.SpecialCategory.Category

You need to revise your split-code. It can be more efficient.
For examples of this, have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-27 : 15:39:46

Peso,
Thanks for the tip. I will give that a try and see how it works.

Thanks also for the tip on the split code. I have another SP that hits it pretty hard to I will definitely look into it further.

Question on your code. I was thinking of using a CASE statement in the WHERE clause as I will run into the variable (or list) could contain the word 'all' as one of the arguments in the list. Any thoughts as to how I would ignore the JOIN, so to speak. More or less I need all the results ?

Just for the record the current select statement is pretty large with about 11 joins already.

Thanks again,
Chris

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 01:57:21

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources