Pass DECLARE parameters in SQL query

Question

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?

You don't need to DECLARE anything when you make a query like that from ADO.Net. Just take out the DECLAREs and SETs and just add the Parameters. — Crowcoder
– Crowcoder, Commented Dec 1, 2018 at 13:25
but if i delete my dateToCheck from the query, my query will fail (or return bad results) — user3127554
– user3127554, Commented Dec 1, 2018 at 13:27
I would consider doing the date calculation in c#, but if you want to do it in SQL then just put that inline - replace ... >= @dateToCheck... with ... >= dateadd(month, 1, datefromparts(@year, @month, 1))... — Crowcoder
– Crowcoder, Commented Dec 1, 2018 at 13:29

Clay · Accepted Answer · 2018-12-01 14:41:21Z

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;

Salman Arshad · Accepted Answer · 2018-12-01 14:32:08Z

0

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);

edited Dec 1, 2018 at 14:32

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

answered Dec 1, 2018 at 13:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Crowcoder Over a year ago

@user3127554 try adding one more closing paren ) before v(dateToCheck)

Collectives™ on Stack Overflow

Pass DECLARE parameters in SQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related