I'm trying to get a stored procedure to work that accepts a multi-value parameter for dates. This isn't in SSRS but I'm trying to use the same approach as I do 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 INT and VARCHAR fields on SSRS report multi-value parameters.
Here is the code I'm using to concatenate the 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 guess I'm not entirely clear on what differs between how I'm formatting the parameter, how SSRS does so for similar parameters (this is the only one I've tried doing from code), and how the Date data type affects required formatting. I'm getting a "Conversion failed when converting date and/or time from character string." error when selecting more than one value.
Edit: As requested, example of foreach loop output:
'9/9/2011', '8/19/2011', '8/12/2011'
Why not use a Table-Valued parameter?
Create a user-defined table-type DateTimes
on SQL
create type DateTimes as table
(
[Value] datetime
)
Then amend 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, a Table-Valued parameter is specified as SqlDbType.Structured
and passed as a DataTable
or DataRowcollection
. So, you can populate it like so:
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 nice and properly typed... and you don't have to do any string parsing or casting.
As a side-note, you might as well go ahead and create Strings
and Integers
types as well; You'll get hooked on TVPs and use them all over the place.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With