Use dynamic value to a variable which is being used in the subsequent dynamic query in my code

Question

This code works when set @ToDate = '20200311' i.e. when I have hardcoded the specific date, but does not work when I want this to always execute till the current date

The line set @ToDate = select .....getdate.. throws error, can you please help me out and tell me what I should do to rectify?

While @@Fetchstatus =0,

begin 
  set @Fromdate = '20180102';
  set @ToDate = select convert (varchar, getdate(), 112);

  while (@Fromdate < @ToDate) begin
         set @StrQuery = 'INSERT into dbo.Tmp_M_Rates (Rate_Date, Rate_D, Entity, cur) values ('
                         +''''+ CAST (@FromDate AS nvarchar) + ''', ''' + Substring (CONVERT (VARCHAR 
                         (10), @Fromdate, 112),3,8) + ''', ''' + @EntityName + ''', ''' + @Cur + 
                          ''')'
      EXEC (@StrQuery)

      set @Fromdate = dateadd(DAY,1,@Fromdate)
end

Bad Habits to Kick : Using EXEC() instead of sp_executesql. Parametrise your statements, don't inject the variables. Also Bad habits to kick : declaring VARCHAR without (length) — Thom A
– Thom A ♦, Commented Mar 11, 2020 at 19:15
Not clear why you need a loop here. Perhaps if you provide sample data and desired results. — John Cappelletti
– John Cappelletti, Commented Mar 11, 2020 at 19:18
It's not really clear why dynamic sql is needed here either; you could just have a fairly basic insert query in the loop. — Uueerdo
– Uueerdo, Commented Mar 11, 2020 at 19:23
@DaleK It throws an error saying something is wrong in my syntax — Berry
– Berry, Commented Mar 11, 2020 at 19:33

Thom A · Accepted Answer · 2020-03-11 19:32:42Z

If Gordon's answer has the correct idea, a far better approach would be to not use any type of recursion. SQL Server is designed for set based methods, and so you should really be using one.

The method you want here is a Tally, which isn't recursive, and isn't limited to 100 loops by default (like an rCTE) because it doesn't loop:

DECLARE @FromDate date = '20180102';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (DATEDIFF(DAY,@FromDate, GETDATE())+1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --1000 rows, add more cross joins to N for more rows
Dates AS(
    SELECT DATEADD(DAY, T.I, @FromDate) AS D
    FROM Tally T)
INSERT INTO dbo.Tmp_M_Rates (Rate_Date, Rate_D, Entity, cur)
SELECT D,
       SUBSTRING(CONVERT(VARCHAR(10), @Fromdate, 112), 3, 8),
       @EntityName,
       @Cur
FROM Dates;

John Cappelletti · Accepted Answer · 2020-03-11 20:02:02Z

0

Just another option with an ad-hoc tally/numbers table

Example

Declare @Date1 date = '2018-01-02'
Declare @Date2 date = getdate()
Declare @Entity varchar(50) ='Entity Name'
Declare @Cur    varchar(50) ='USD'

--INSERT into dbo.Tmp_M_Rates (Rate_Date, Rate_D, Entity, cur) 
Select Rate_Date = D
      ,Rate_D    = dateadd(DAY,1,D)
      ,Entity    = @Entity
      ,Cur       = @Cur
 From (
         Select Top (DateDiff(DAY,@Date1,@Date2)+1) 
               D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1) 
         From  master..spt_values n1,master..spt_values n2
      ) A

Generates the Following for INSERT

edited Mar 11, 2020 at 20:02

answered Mar 11, 2020 at 19:34

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

3 Comments

Berry Over a year ago

But the ToDate is not fixed here. If it was fixed , the query works fine when i give a values. But im facing the issue when I want to generate the dynamic query till the current date.ie. today when i run automatically it shuld pick today's system date as 'to date' and tomorrow it should pick 'tomorrow's date' and so on.

John Cappelletti Over a year ago

@Berry I was guessing how about Date2 = cast(getdate() as date)

John Cappelletti Over a year ago

@Berry should the second date column be dateadd(DAY,1,D) ? Again without a sample it is hard to determine the actual results

Gordon Linoff · Accepted Answer · 2020-03-11 19:23:13Z

0

You can phrase this as a single query with no dynamic SQL:

WITH dates as (
      SELECT CONVERT(date, '20180102') as dte
      UNION ALL
      SELECT dateadd(day, 1, dte)
      FROM CTE
      WHERE dte <= GETDATE()
     )
INSERT into dbo.Tmp_M_Rates (Rate_Date, Rate_D, Entity, cur)
    SELECT dte, SUBSTRING(CONVERT(VARCHAR(10), @Fromdate, 112), 3, 8),
           @EntityName, @Cur
    FROM dates
    OPTION (MAXRECURSION 0);

I would strongly recommend that you take this or a similar approach.

answered Mar 11, 2020 at 19:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Thom A Over a year ago

Why an rCTE? For 2018 to date, that's over 700 loops; it's really not much better than a WHILE. A Tally would be so much faster. An rCTE, like the name suggests, is still a form of recursion; just like a Loop.

Gordon Linoff Over a year ago

@Larnu . . . A single query is much better than a while. Admittedly, the comparison is different, but recursive CTEs have very reasonable performance -- a bit worse than say a numbers table -- but perfectly reasonable for most problems. Here is an example: strictlysql.blogspot.com/2010/01/….

Thom A Over a year ago

I disagree. An rCTE is a very slow method. Hidden RBAR: Counting with Recursive CTE's, rCTE vs Tally. A TAlly is significantly faster.

Collectives™ on Stack Overflow

Use dynamic value to a variable which is being used in the subsequent dynamic query in my code

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related