Split Function Usage for Multiple Multi-Select Parameters

Question

Split Function Usage for Multiple Multi-Select Parameters

Tek

SSCommitted

Points: 1697
More actions
January 5, 2017 at 8:56 am

#333552

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!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

DarthBurrito (1/5/2017)
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!

Assuming they are split on just one thing (like a comma or pipe) then one function will do. Or you can have a function that takes a parameter of what the delimiter is.

Please search on SSC.com forums for Delimited8KSplit for a very slick and efficient implementation for this functionality.

Also, I would look into using Table Valued Parameters since you are switching to sprocs.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Tek SSCommitted Points: 1697 More actions · Answer 2

TheSQLGuru (1/5/2017)
DarthBurrito (1/5/2017)
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!
Assuming they are split on just one thing (like a comma or pipe) then one function will do. Or you can have a function that takes a parameter of what the delimiter is.
Please search on SSC.com forums for Delimited8KSplit for a very slick and efficient implementation for this functionality.
Also, I would look into using Table Valued Parameters since you are switching to sprocs.

Thanks! Yeah, it's using comma as the split. Just wasnt sure if using one split function would conflict when using it on two multi-select parameters on the same report.

I'll look into the Delimited8KSplit. I'm sure this would be very helpful for any future report development. Thanks.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

You can find the article on the improvements made to the DelimitedSplit8K function with performance tests against other types of splitters as well as the code (see the RESOURCES section at the end of the article) at the following URL.

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

My suggestion is that you insert parameter values into a table for each parameter.

Basically, your stored procedures would have a format similar to this:

CREATE PROCEDURE SomeReport (

@Param1 varchar(8000),

@Param2 varchar(8000)

)

AS

CREATE TABLE #Params1( param_value int);

CREATE TABLE #Params2( param_value varchar(10));

INSERT INTO #Params1

SELECT TRY_CONVERT(int, Item)

FROM dbo.DelimitedSplit8K(@Param1, ',') s;

INSERT INTO #Params2

SELECT Item

FROM dbo.DelimitedSplit8K(@Param2, ',') s;

SELECT st.SomeColumn

FROM SomeTable st

WHERE st.FilterColumn1 IN (SELECT p.param_value FROM #Params1 p)

AND st.FilterColumn2 IN (SELECT p.param_value FROM #Params2 p);

GO

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246978 More actions · Answer 5

Luis Cazares (1/5/2017)
My suggestion is that you insert parameter values into a table for each parameter.
Basically, your stored procedures would have a format similar to this:
CREATE PROCEDURE SomeReport (
@Param1 varchar(8000),
@Param2 varchar(8000)
)
AS
CREATE TABLE #Params1( param_value int);
CREATE TABLE #Params2( param_value varchar(10));
INSERT INTO #Params1
SELECT TRY_CONVERT(int, Item)
FROM dbo.DelimitedSplit8K(@Param1, ',') s;
INSERT INTO #Params2
SELECT Item
FROM dbo.DelimitedSplit8K(@Param2, ',') s;
SELECT st.SomeColumn
FROM SomeTable st
WHERE st.FilterColumn1 IN (SELECT p.param_value FROM #Params1 p)
AND st.FilterColumn2 IN (SELECT p.param_value FROM #Params2 p);
GO

Hi Luis, I've used a variation on this method where

a) I put a clustered PK on the temp tables, and

b) I join 'SomeTable' to the temp tables to implicitly apply the filtering, rather than the WHERE clause method you've demonstrated.

Have you also tried this? I was wondering if one performed consistently better.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I've used the clustered PK, but I don't remember if there was an actual improvement or not. There might be, but with a small number of rows (as parameters usually are) I can't really be sure.

For the other option, I don't like to use JOINs just as filters. One reason is that when using permanent tables, I've been affected by duplicate values. The other reason is that is clearer that the table is just there as a filter and not as a way to retrieve data (columns).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Phil, why would you put a clustered index on the temp table?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mikevessey Valued Member Points: 66 More actions · Answer 8

why not use a TABLE VALUE PARAMETER in your stored proc

simply use the CREATE TYPE statement (to create a table type), then create your proc using the table type as a parameter

CREATE PROC MyProc @mytab MYTYPE READONLY as

select * from sometable s inner join @mytab t on t.id=s.id

then there is no need to parse a CSV list

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

mikevessey (1/5/2017)
why not use a TABLE VALUE PARAMETER in your stored proc
simply use the CREATE TYPE statement (to create a table type), then create your proc using the table type as a parameter
CREATE PROC MyProc @mytab MYTYPE READONLY as
select * from sometable s inner join @mytab t on t.id=s.id
then there is no need to parse a CSV list

I'm not sure that SSRS can handle table valued parameters.

If you know how to do it, please let us know how or point us to a resource for that.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mikevessey Valued Member Points: 66 More actions · Answer 10

this is the first one I came across on google

http://gruffcode.com/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services/

Phil Parkin SSC Guru Points: 246978 More actions · Answer 11

TheSQLGuru (1/5/2017)
Phil, why would you put a clustered index on the temp table?

1) Helps validation by ensuring there are no duplicates (of course, you may not always want this)

2) Maybe helps performance, if there are a large number of elements (this is not something I have tested much)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

mikevessey Valued Member Points: 66 More actions · Answer 12

lets be clear on that point....

a clustered index does not prevent duplicates

a primary key (normally also the clustered index) or also a unique constraint will prevent duplicates

the clustered index is just the leaf node of the B-tree, not the physical storage order, but a logical storage order of the data in the table

Phil Parkin SSC Guru Points: 246978 More actions · Answer 13

mikevessey (1/6/2017)
lets be clear on that point....
a clustered index does not prevent duplicates
a primary key (normally also the clustered index) or also a unique constraint will prevent duplicates
the clustered index is just the leaf node of the B-tree, not the physical storage order, but a logical storage order of the data in the table

Sounds like you're answering an interview question! I actually misread Kevin's message as 'Why put a clustered PK on a temp table?' - anyhow that's the question I answered.

If you are saying that there is no relation between physical storage order and clustered index order, I disagree.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

mikevessey Valued Member Points: 66 More actions · Answer 14

I've been tripped up by this before too

The key point is PHYSICAL .

A clustered index does not determine the order it is stored on disk.

I went to a lecture by itzig ben-gan in 2008 , he showed that the data page that the row is stored on is controlled by your san or disk controller. In the case of mixed extents, you have multiple tables in one 64 kb block.

Your disk is random access, therefore it is not possible to say any data is stored in a linear format.

The clustered index is the sort order of the b-tree, not the order of storage on disk... But let's not pursue this, the original posters question is the point