Comment gérer efficacement les paramètres SSRS multi-valués

Petit rappel, lorsque que plusieurs valeurs sont sélectionnées dans un paramètre SSRS, ce dernier nous renvoie une chaîne de caractères dans laquelle sont concaténées toutes les valeurs clés, séparées par des virgules.

Exemple : @myParam = ‘test 1,test2,test3,test 4,test 5’

Alors pour requêter vos données, par pitié, éviter les choses bien moches tel que :

  • du SQL dynamique dans une procédure stockée
  • du SQL dynamique embarqué dans votre dataset SSRS à l’aide d’une expression vous permettant de splitter les valeurs multiple des paramètres, …

Ces solutions hasardeuses créent des failles de sécurité; car qui dit SQL dynamique, dit injection de code possible => à éviter donc, comme la peste.
J’en profite pour glisser, au passage, quelques bonnes pratiques :

  • proscrire l’usage de SQL dynamique sur vos instances à l’aide de « policies »
  • arrêter d’embarquer du code SQL comme des bœufs dans vos rapports => utiliser des procédures stockées.

Mais alors comment faire pour gérer des paramètres SSRS multi-valués dans vos requêtes SQL ?

Afin de traiter ce type de paramètres, il suffit tout simplement de créer une table-valued function permettant d’extraire les différentes valeurs contenues dans une chaîne de caractères et délimitées par un caractère. La dite fonction est assez simple à mettre en œuvre :

</pre>
CREATE FUNCTION [dbo].[ufn_SplitMultiValue]
(
 @inputValues NVARCHAR(MAX),
 @delimiter NVARCHAR(255)
)
RETURNS @occurs TABLE (value NVARCHAR(MAX))
AS
BEGIN
 DECLARE @word NVARCHAR(MAX)

WHILE (CHARINDEX(@delimiter, @inputValues, 1) > 0)
 BEGIN
 SET @word = SUBSTRING(@inputValues, 1 , CHARINDEX(@delimiter, @inputValues, 1) - 1)
 SET @inputValues = SUBSTRING(@inputValues, CHARINDEX(@delimiter, @inputValues, 1) + 1, LEN(@inputValues))
 INSERT INTO @occurs VALUES(@word)
 END

INSERT INTO @occurs SELECT @inputValues

RETURN
END
<pre>

L’utilisation de celle-ci est la plus simple qu’il soit :

</pre>
DECLARE @myParam NVARCHAR(MAX) = 'test1,test2,test3,test4,test5'
DECLARE @myDelimiter NVARCHAR(255) = ','

SELECT value FROM [dbo].[ufn_SplitMultiValue](@myParam,@myDelimiter)
<pre>

Il ne reste plus qu’à utiliser cette fonction dans vos procédures stockées dédiées à vos rapports SSRS :

</pre>
CREATE PROCEDURE [ssrs].[usp_MyReportDataGet]
(
 @myParam NVARCHAR(MAX)
) AS
BEGIN
 SELECT tbl.col1,tbl.col2
 FROM myTable
 WHERE myColumnFilter IN (SELECT VALUE FROM dbo.ufn_SplitMultiValue(@myParam,','))
END
<pre>

On a donc une solution toute simple et propre, sans bidouille aucune.

Ayant épuisé les devises Shadoks, je finirai ce post par une citation d’un grand philosophe contemporain méconnu :

 » Je suis fasciné par l’air.
Si on enlevait l’air du ciel, tous les oiseaux tomberaient par terre…. Et les avions aussi…
En même temps l’air tu peux pas le toucher… Ça existe et ça existe pas…
Ça nourrit l’homme sans qu’il ait faim… It’s magic…
L’air c’est beau en même temps tu peux pas le voir, c’est doux et tu peux pas le toucher…
L’air c’est un peu comme mon cerveau… »
J.C.V.D

8 réflexions sur “Comment gérer efficacement les paramètres SSRS multi-valués

  1. Comme indiqué par laurent (Linkedin), on peut aussi se passer de la fonction par :

    SELECT tbl.col1,tbl.col
    FROM myTable
    WHERE charindex(','+ myColumnFilter+',' , ','+@myParam+',')>0

    L’avantage d’utiliser la fonction est qu’on peut plus facilement adresser des cas d’utilisation exotiques, comme un paramètre en saisie libre dans lequel l’utilisateur pourrait saisir des valeurs multiples séparées par des virgules ou tout autre délimiteur et sur lequel on voudrait effectuer quelques transformations basiques (manipulation de chaîne, conversion, etc…)

      • Finalement, pas besoin d’environnement de test, une remontée client c’est bien plus concret. Sur un rapport RS utilisant une procédure stockée, le temps d’exécution moyen était d’1 minute, alors qu’exécutée indépendamment, la requête s’exécutait en 6s. Un alter de la procédure stockée en ajoutant le query hint OPTION RECOMPILE a résolu le problème => temps d’exécution final du rapport 7s.
        Le ‘Parameter Sniffing’ se constate aisément en prenant le plan d’exécution de la proc stock avant et après modification.
        Merci David🙂

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s