CODEWITHSUNDEEP
c#sqllinq-to-sqlstored-procedures
Eric
Eric

Reputation: 2128

Linq to Sql: Generic Stored Procedures

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.

Upvotes: 1

Views: 809

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

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.

Upvotes: 1

Schultz9999
Schultz9999

Reputation: 8926

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)

Upvotes: 2

Related Questions