存储过程，传递日期数组，逗号分隔.转换错误

Question

我正在尝试向我的存储过程发送一个逗号分隔的日期字符串，以便在in子句中使用。然而，我得到了错误"转换失败时，从字符串转换日期和/或时间。"

我正试着用这些日期来寻找匹配的价格。C#：

 StringBuilder LastDaysOfEveryMonth = new StringBuilder();
 DataAccess da = new DataAccess();
 SqlCommand cm = new SqlCommand();
 cm.CommandType = CommandType.StoredProcedure;
 var pList = new SqlParameter("@DateList", DbType.String);
 pList.Value = LastDaysOfEveryMonth.ToString();
 cm.Parameters.Add(pList);
...
 cm.CommandText = "spCalculateRollingAverage";
 DataSet ds = da.ExecuteDataSet(ref cm);

调试时，传递的字符串的值为：

'2013-07-31','2013-08-30','2013-09-30','2013-10-31','2013-11-29','2013-12-31',
'2014-01-31','2014-02-28','2014-03-31','2014-04-03',

使用DbType字符串和SQLDbType NvarChar。

任何建议都将不胜感激！SQL:

CREATE PROCEDURE [dbo].[spCalculateRollingAverage] 
@StartDate DateTime,
@EndDate DateTime,
@Commodity nvarchar(10),
@PeakType nvarchar (10),
@BaseID int,
@NumberOfMonths int,
@DateList nvarchar(MAX)  
AS
    BEGIN
    select TermDescription,ContractDate,Price,SortOrder into #tbtp from BaseTermPrice 
    inner hash join Term 
    on 
    Term.TermID = BaseTermPrice.TermID
    where 
    BaseID = @BaseID and ((@PeakType IS NULL and PeakType is null) or 
    (@PeakType IS     NOT NULL and PeakType=@PeakType))
    and ((@DateList IS NULL and ContractDate between @StartDate and @EndDate) 
     or (@StartDate IS NULL and ContractDate in (@DateList)))
    order by
    ContractDate,SortOrder

Answer 1

不能像这样在IN子句中使用varchar变量。您必须将其添加到动态SQL中执行，或者将其拆分为临时表/temp变量。

例如，使用这个SplitString函数，您可以执行以下操作：

or (@StartDate IS NULL and ContractDate in 
          (SELECT Name from dbo.SplitString(@DateList))))