Split Function

I have the task to convert our company reports from query embedded reports to stored procedures.

Some of the reports require multi-select parameters. I'm currently using a function to functions the way I like. However, I've been using said function for reports that have one parameter that uses multi-select.

So here's my question. Would it smart to create an identical split functions for different types of parameters. For example, when a report has a parameter for Locations and one for Clerk? Or is it safe to use the same one split function for both Locations and Clerks?


Could you perhaps post some sample data and the split function you are using? Also, have you read:


Another option could be sending in the multi-select parameters as xml and parse? dump into temp table

create proc products_sp
	@categories xml,
	@brands xml,
	@colors xml,
	@prices xml
		@category xml
create table #categories(categoryid int not null);
create table #brands(brandid int not null);
create table #colors(colorid int not null);
create table #prices(priceid int not null);

insert into #categories
select  T.C.value('(CategoryId/text())[1]','varchar(500)') as CategoryId
  from @categories.nodes('/Categories/Category') as T(C) 

  from dbo.products p
  join dbo.brands b on p.Brandid = b.brandid
  join dbo.ProductColors pc on pc.productId = p.Productid
  join dbo.DimColors dc on pc.Colorid = = dc.Colorid
  join dbo.ProductPrices pp on pp.productId = p.productId
  join dbo.ProductCategories pcg on pcg.productId = p.productId
  left join #categories c on pcg.categoryid = c.categoryid
  left join #brands b on b.Brandid = p.brandid