CODEWITHSUNDEEP
sqlsql-server-2008t-sqlselectstored-procedures
Neo
Neo

Reputation: 16219

How to pass more than one values as parameter to sql statement?

I have one big SQL query and I want to pull out some data using that query 

declare @Period VARCHAR(10)  = 'MTD'
declare @Date DATETIME = '2011-08-31'

and I have a big select statement where I'm passing above parameters and it executes the output.

Now I have 10 different dates which I need to pass here each time to see the result.

How can I pass those date to above parameter declare @Date DATETIME how can I hard code it ?

So my desired output will be for those selected dates, give me hint for at least 3 dates ?

Upvotes: 0

Views: 856

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Use a table-valued parameter. First, create a type:

CREATE TYPE dbo.Dates AS TABLE(d DATE);

Now your stored procedure can take this type as a parameter:

CREATE PROCEDURE dbo.whatever
  @d dbo.Dates READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT t.columns
    FROM dbo.tableName AS t
    INNER JOIN @d AS d
    ON t.[date] = d.d;
END
GO

Then from your application you can pass this parameter in as a DataTable, for example.

Upvotes: 6

Related Questions