1

I am using Linq-to-Sql for a C# application and am currently working on some stored procedures. The application is for a newspaper, and a sample stored procedure is the following:

ALTER PROCEDURE dbo.Articles_GetArticlesByPublication
   @publicationDate date
AS
   SELECT 
      *
   FROM 
      Articles
   WHERE
      Articles.PublicationDate=@publicationDate

Anyway, this query gets all of the articles where the publication date is equal to the argument (publicationDate). How can I alter this so that the argument can handle multiple publication dates?

Also, I'd prefer not to use "BETWEEN," rather, I want to pick and choose dates.

Eric
  • 2,098
  • 4
  • 30
  • 44
  • would a from and through criteria work (contiguous dates) or do you need a list of unrelated dates? Also, do you have times stored in your datetime PublicationDate field or just a date, like 2011-01-03 00:00? – Tahbaza Jan 04 '11 at 03:44
  • They are just dates, so I suppose I could change it to "@publicationDate date." I just edited my post to answer your first question. Ideally I'd like to have it with unrelated dates. – Eric Jan 04 '11 at 03:45
  • What version of SQL are you working on? – Conrad Frix Jan 04 '11 at 05:55

2 Answers2

2

There is no native support to pass arrays/lists to the TSQL SP. However you can use XML or varchar data types. I'd prefer XML because you can easily create a table variable and select all values from passed XML into it and then use IN statement in WHERE clause (this is just a concept SQL):

CREATE STORED PROCEDURE foo AS
    @dates XML
BEGIN
...
DECLARE @datesTable TABLE ( [d] date )

INSERT INTO @datesTable
SELECT d FROM OPENXML (@dates, '/ROOT/date',1) WITH (d date)

...
SELECT... WHERE date IN (SELECT d FROM @datesTable)
Schultz9999
  • 8,717
  • 8
  • 48
  • 87
1

If you're using SQL Server 2008 you can use table valued parameters to pass multiple values to a stored procedure with XML or Comma delimited lists, but its not supported with LINQ to SQL so you'd have to use ADO.NET directly with .NET 3.5 and up.

See this answer from marc_s on why it's unavailable in Linq to SQL.

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155