CODEWITHSUNDEEP
c#sql-serverdatetime
user3127554
user3127554

Reputation: 577

Pass DECLARE parameters in SQL query

I have the following code: 

SqlCommand command = new SqlCommand(
    @"DECLARE @month int, @year int, @dateToCheck datetime;
      SET @month = @month;
      SET @year = @year;
      SET @dateToCheck = dateadd(month, 1, datefromparts(@year, @month, 1))

      SELECT p.name, dtc.cost_price, p.date_created
      FROM [dbo].[Company_Local_Invoice_] claidig
      JOIN Type_Company dtc on claidig.ID = dtc.id 
      WHERE p.date_created < @dateToCheck
      AND (p.date_left is null or p.date_left >= @dateToCheck)", conn);

command.Parameters.Add("@month", SqlDbType.Int).Value = month;
command.Parameters.Add("@year", SqlDbType.Int).Value = year;

The problem is that I can't seem to pass my SET parameters using command.Parameter.Add() .

The error that I get is:

The variable name '@month' has already been declared. Variable names must be unique within a query batch or stored procedure.

Why is this and how can I work around this?

Upvotes: 1

Views: 1822

Answers (2)

Clay
Clay

Reputation: 5084

The point Gordon is making is that when you pass parameters to a sql string, it prepends the 'declare' statements from the parameter definitions. So, you don't need to do the declare for anything that's coming in as parameters. You still need to declare any variable that gets computed from the parameters though.

var commandText = @"

  declare @dateToCheck datetime

  set @dateToCheck = dateadd(month, 1, datefromparts(@year, @month, 1))

  select 
    p.name, dtc.cost_price, p.date_created
  from 
    dbo.[Company_Local_Invoice_] claidig
    inner join 
    Type_Company dtc 
    on c
      laidig.ID = dtc.id 
  where
    p.date_created < @dateToCheck
    and 
    (
      p.date_left is null 
      or 
      p.date_left >= @dateToCheck
    )";

var command = new SqlCommand(commandText, conn);
command.Parameters.Add("@month", SqlDbType.Int).Value = month;
command.Parameters.Add("@year", SqlDbType.Int).Value = year;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Just pass in the parameters and do the calculations in the query:

SELECT p.name, dtc.cost_price, p.date_created
FROM [dbo].[Company_Local_Invoice_] claidig
JOIN Type_Company dtc ON claidig.ID = dtc.id
CROSS APPLY (VALUES 
    (dateadd(month, 1, datefromparts(@year, @month, 1)))
) v(dateToCheck)
WHERE p.date_created < v.dateToCheck AND
      (p.date_left is null or p.date_left >= v.dateToCheck);

Upvotes: 0

Related Questions