select all filter by categories

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 2008 Forums

Transact-SQL (2008)

select all filter by categories

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2013-10-24 : 09:15:02

Hi, I want to make a SP which will select all articles which belong to specified categories. So I'm sending the categories as a csv string, like so 3,5,11.

So what I have is this:

SELECT
....
From
Articles
where
(@Categories = '' OR ArticleID in (
SELECT DISTINCT ArticleID FROM ArticleCategories ac WHERE
(ac.CategoryID IN (SELECT intValue FROM dbo.csvToInt(@Categories)))
))

And csvToInt is a function which looks like this:
CREATE Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

So is this the best way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 11:53:10

yes. if you want you may use more optimised UDF for sring split like

http://www.sqlservercentral.com/articles/Tally+Table/72993/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources