Paramètre de date à valeurs multiples dans une procédure stockée?

J’essaie de faire fonctionner une procédure stockée qui accepte un paramètre à valeurs multiples pour les dates. Ce n’est pas dans SSRS mais j’essaie d’utiliser la même approche qu’avec:

ALTER PROCEDURE spSelectPlacementData ( @ClientID SMALLINT, @SourceFileDates VARCHAR(MAX) ) AS BEGIN SELECT (snip) FROM [APS].[dbo].[Account] A WHERE ClientID = @ClientID AND A.[SourceFileDate] IN (SELECT * FROM dbo.Split(@SourceFileDates)) END 

J’utilise cette approche avec les champs INT et VARCHAR sur les parameters multivaleurs du rapport SSRS.

Voici le code que j’utilise pour concaténer les SourceFileDates:

  ssortingng sourceFileDates = ""; foreach (DateTime file in job.sourceFiles) { if (file == job.sourceFiles.Last()) { sourceFileDates += "'" + file.ToSsortingng("d") + "'"; } else { sourceFileDates += "'" + file.ToSsortingng("d") + "', "; } } selectRunCommand = new SqlCommand("spSelectPlacementData", sqlConnection); selectRunCommand.CommandType = CommandType.StoredProcedure; selectRunCommand.Parameters.Add("@ClientID", SqlDbType.SmallInt); selectRunCommand.Parameters["@ClientID"].Value = job.clientID; selectRunCommand.Parameters.Add("@SourceFileDates", SqlDbType.VarChar); selectRunCommand.Parameters["@SourceFileDates"].Value = sourceFileDates; 

En utilisant cette fonction dbo.Split, j’ai récupéré en ligne:

 /****** Object: UserDefinedFunction [dbo].[Split] Script Date: 09/20/2011 11:16:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[Split] /* This function is used to split up multi-value parameters */ ( @ItemList VARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @IDTable TABLE (Item VARCHAR(MAX) collate database_default ) AS BEGIN DECLARE @tempItemList VARCHAR(MAX) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item VARCHAR(MAX) SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter) SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) INSERT INTO @IDTable(Item) VALUES(@Item) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END RETURN END 

Je suppose que je ne suis pas tout à fait clair sur ce qui diffère entre la façon dont je formate le paramètre, comment SSRS le fait pour des parameters similaires (c’est le seul que j’ai essayé de faire à partir de code) et comment le type de données Date a une incidence sur mise en forme. Je reçois un message “Echec de la conversion lors de la conversion de la date et / ou de l’heure en chaîne de caractères.” erreur lors de la sélection de plusieurs valeurs.

Edit: comme demandé, exemple de sortie de boucle foreach:

‘9/9/2011’, ‘8/19/2011’, ‘8/12/2011’

Pourquoi ne pas utiliser un paramètre Table-Valued ?

Créer un type de table DateTimes défini par l’utilisateur sur SQL

 create type DateTimes as table ( [Value] datetime ) 

Puis modifiez votre procédure stockée:

 ALTER PROCEDURE spSelectPlacementData ( @ClientID SMALLINT, @SourceFileDates DateTimes readonly -- must be readonly ) 

Vous pouvez maintenant traiter @SourceFileDates comme une variable table à lecture seule.

Lorsque vous spécifiez vos parameters SqlCommand , un paramètre Table-Valued est spécifié en tant que SqlDbType.Structured et est transmis en tant que DataTable ou DataRowcollection . Donc, vous pouvez le peupler comme suit:

 var sourceFileDates = new DataTable(); sourceFileDates.Columns.Add("Value", typeof(DateTime)); foreach (DateTime file in job.sourceFiles) { sourceFileDates.Rows.Add(file); } selectRunCommand.Parameters.Add(new SqlParameter { ParameterName = "@SourceFileDates", Value = sourceFileDates, SqlDbType = SqlDbType.Structured // make sure you specify structured }); 

Maintenant, tout est bien et correctement typescript … et vous n’avez pas à parsingr ni à lancer de chaînes.

En passant, vous pouvez également créer des types Ssortingngs et Integers . Vous allez devenir accro aux TVP et les utiliser partout.

SSRS sortingche un peu parce qu’il contrôle les entrées … il n’est pas tellement préoccupé par les attaques par injection SQL. Avec une procédure stockée, cela serait un peu plus difficile à faire.

Ce qui a bien fonctionné pour moi lorsque j’avais besoin d’envoyer plusieurs valeurs dans un seul argument en 2005, je les envoyais sous forme de chaîne XML, comme suit:

  2011-01-23 2011-02-24  

et ensuite traiter ceci comme une table dans la fonction:

 select xavalue('.', 'datetime') as myDate from @XMLArg.nodes('/dates/date') x(a); 

maintenant, vous devriez avoir vos données sous forme de valeur table. (la syntaxe est peut-être un peu décalée, c’est une question qui m’est posée)