Split Function

Transact-SQL
1

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?

Thanks!

2

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

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

3

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

create proc products_sp
as
(
	@categories xml,
	@brands xml,
	@colors xml,
	@prices xml
)
begin
	/*
		@category xml
    	<Categories>   
	  <Category>
		<CategoryId>1</CategoryId>			
	  </Category> 
	  <Category>
		<CategoryId>2</CategoryId>    
	  </Category> 
	</Categories>
*/
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) 

select 
  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
end