A multi-valued date parameter in a stored procedure?

Question

I am trying to get a stored procedure to work, which takes a parameter with multiple values ​​for dates. This is not in SSRS, but I'm trying to use the same approach as me with it:

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 

I use this approach with the INT and VARCHAR fields in multi-valued SSRS report parameters.

Here is the code I use to concatenate SourceFileDates:

  string sourceFileDates = ""; foreach (DateTime file in job.sourceFiles) { if (file == job.sourceFiles.Last()) { sourceFileDates += "'" + file.ToString("d") + "'"; } else { sourceFileDates += "'" + file.ToString("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; 

Using this dbo.Split function, I grabbed online:

 /****** 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 

I think I don’t quite understand what makes me different from how I format the parameter, how SSRS does it for similar parameters (this is the only one I tried to do from the code) and how Date type data affects the formatting I need. I get a "Conversion error while converting date and / or time from character string." Error selecting more than one value.

Edit: upon request, an example of the output of a foreach loop:

'9/9/2011', '8/19/2011', '8/12/2011'

Answer 1

Why not use the Table-Valued parameter ?

Create a custom table type DateTimes in SQL

 create type DateTimes as table ( [Value] datetime ) 

Then modify your stored procedure:

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

Now you can treat @SourceFileDates as a readonly table variable.

When specifying your SqlCommand parameters, the Table-Valued parameter is specified as SqlDbType.Structured and passed as a DataTable or DataRowcollection . So you can fill it like this:

 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 }); 

Now everything is well and correctly printed ... and you do not need to do any parsing or casting.

As a side note, you can also continue and create the Strings and Integers types; You will be connected to the TVP and use them everywhere.

Answer 2

SSRS is a little cheating because it controls the input ... he is not so much interested in SQL Injection attacks. Using a stored procedure will make it a little harder to do.

What worked for me, when I needed to send multiple values ​​to a single argument in 2005, I would send them as an XML string, for example:

 <dates> <date>2011-01-23</date> <date>2011-02-24</date> </dates> 

and then consider this as a table in a function:

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

You should now have your data as tabular. (the syntax may be a little off, it's not in my head)