SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multi-Select Parameters for Reporting Services


Multi-Select Parameters for Reporting Services

Author
Message
Philippe Cand
Philippe Cand
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 294
Hello,
I have been using all these solutions and finally settled for a CLR function that I found on the Internet.
It works great for me.
Regards,
Philippe

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, TableDefinition = "SQLStr nvarchar(4000)", FillRowMethodName = "FillSplitCommaDelimitedStringToStr")]
public static System.Collections.IEnumerable clrfn_SplitCommaDelimitedString(SqlString str)
{
string x = str.Value;
if (!string.IsNullOrEmpty(x))
{
return x.Split(',');
}
else
{
return null;
}
}
private static void FillSplitCommaDelimitedStringToStr(object obj, out SqlString str)
{
if (obj != null)
str = (String)(obj);
else
str = String.Empty;
}
};

''I call it like that
Where (@PTI2 = '##' or b.PTI2_Cd in (select ltrim(SQLstr) from ONGlobals.dbo.clrfn_SplitCommaDelimitedString(@Pti2) ) )
''The ## is used for cases where we want all values.

BI Guy
Luke-458024
Luke-458024
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 9

If you want to use an IN try this?

WHERE RealTableColumn IN (SELECT TempTableColumn FROM TempTable)

EXISTS can be fast too but not sure in this scenario.

WHERE EXISTS (SELECT TempTableColumn FROM TempTable WHERE TempTableColumn = RealTableColumn)

However a JOIN on an indexed TempTableField should be as fast as anything in my experience.

I would normally use the IN option as it is clearer.


Wapper
Wapper
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 88

If you want to avoid dynamic SQL, you could use an UDF that will convert comma-delimited string to a table-type variable, and then use IN as usual.

SELECT * FROM customers

WHERE ISNULL(@cust_id_multiple, '') = '' OR customers.cust_id IN (SELECT value FROM dbo.LIST_TO_TABLE(@cust_id_multiple))

Below is a sample UDF that does the trick.

--converts list of values to a temporary table. Used to avoid dynamic SQL statements when IN clause is used as filter expression
--sList: list of numeric values in comma-separated form, to be converted to table
--return value: table-type variable containing one column. The table is filled with numeric values from @sList
CREATE FUNCTION LIST_TO_TABLE (@sList AS VARCHAR(255))
RETURNS @retTable TABLE(value VARCHAR(50)) AS

BEGIN

IF @sList IS NULL RETURN

--preparing the input list, removing IN keyword and parenthesis
SET @sList = REPLACE(@sList, 'IN(','')
SET @sList = REPLACE(@sList, 'IN (','')
SET @sList = REPLACE(@sList, '(','')
SET @sList = REPLACE(@sList, ')','')
SET @sList = LTRIM(RTRIM(@sList))


--scrolling thru values in @sList and populating temporary table
DECLARE @Index INT, @Delimiter CHAR(1)
DECLARE @Result VARCHAR(255)


SET @Delimiter = ','

WHILE @sList <> ''
BEGIN
SET @Index = CHARINDEX(@Delimiter, @sList)
IF @Index <> 0
BEGIN
SET @Result = LEFT(@sList, @Index - 1)
SET @sList = SUBSTRING(@sList, @Index + 1, LEN(@sList))
END
ELSE
BEGIN
SET @Result = @sList
SET @sList = ''
END

INSERT @retTable SELECT @Result
END

RETURN

END



Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi Jose,

We tried to split the string into records, and insert it into a temp table yes, and it had some perfomance boost but the cost of splitting the string and inserting into a temp table cause performance to be less than using dynamic sql...


Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi SDM,

It could well be that my results were affected by our table size and parameter length as it was a fairly large table and many parameter options (strings) selected. I'm sure a smaller table and less parameters would have different results. We decided to stick with the dynamics SQL as to advantages out weigh the disadvantages.

In the query plan computations stood out most (as expected) and the join was very in-efficient, which is what I expected as well.


Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi Jon,

Interesting idea. Have you tested this against large tables? This would result in a table scan which might have a big impact on performance...


Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi Jim,

Your approach is the same as what we did, and found it to be highly inefficient, have you done some perfonace testing on big tables and wide range of parameter selections?


Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi dph,

Have you done performance testing on this with large tables? I try to avoid "LIKE" statements as much as possible as they have a tendancy to cause table scans. I would be interested to see your performance testing results.


Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi sebastian,

I don't normally use indexes on temp tables as building the index is often more costly than not using it. In this case we did test it with, and without indexes and the overall performance was better without the index.

My rule of thumb for indexes is to only create then if they will be used more than once, then the cost of building the index will be less than scanning the table. With temp tables this is mostly true.


Adriaan Davel
Adriaan Davel
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 13

Hi Philippe,

Thanks for the post. This was the one option we considered but did not test as we were running on a SQL2000 database and I had never worked with CLR on SQL2000. I would imagine that this would be a good solution as C\C++\C# is normally better as string manipulation than SQL.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search