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](https://sqlblog.org/2011/09/17/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)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) — Thom A, Mar 11 '20 at 19:15
Not clear why you need a loop here. Perhaps if you provide sample data and desired results. — John Cappelletti, Mar 11 '20 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, Mar 11 '20 at 19:23
@DaleK It throws an error saying something is wrong in my syntax — Berry, Mar 11 '20 at 19:33
@JohnCappelletti So, the requirement here is that one of the tables has values only for weekdays, and not for weekends. But values will have to be populated for weekends as well. And also the friday's value should be for saturdays & sundays and hence the loop — Berry, Mar 11 '20 at 19:33
To fix your error set @ToDate = ( select convert (varchar, getdate(), 112) ) ; Notice that we () the select. Also it criminal to store dates as strings like 20200311 — John Cappelletti, Mar 11 '20 at 19:57
@JohnCappelletti, thanks a lot, yes that has now worked and the expected results are appearing fine, thanks a ton — Berry, Mar 12 '20 at 06:08

score 1 · Answer 1 · answered Mar 11 '20 at 19:32

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;

score 0 · Answer 2 · answered Mar 11 '20 at 19:23

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 '20 at 19:23

Gordon Linoff

1,242,037
58
646
786

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. – Thom A Mar 11 '20 at 19:24
@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: http://strictlysql.blogspot.com/2010/01/recursive-cte-vs-temp-table-performance.html. – Gordon Linoff Mar 11 '20 at 20:19
I disagree. An rCTE is a very slow method. [Hidden RBAR: Counting with Recursive CTE's](https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes), [rCTE vs Tally](https://wp.larnu.uk/speed-test-rcte-vs-tally/). A TAlly is *significantly* faster. – Thom A Mar 11 '20 at 21:18

John Cappelletti · Accepted Answer · 2020-03-11T20:02:02.427

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 '20 at 20:02

answered Mar 11 '20 at 19:34

John Cappelletti

79,615
7
44
66

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. – Berry Mar 11 '20 at 19:43
@Berry I was guessing how about Date2 = cast(getdate() as date) – John Cappelletti Mar 11 '20 at 19:49
@Berry should the second date column be dateadd(DAY,1,D) ? Again without a sample it is hard to determine the actual results – John Cappelletti Mar 11 '20 at 19:50

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

3 Answers3