0

I wanted to know if there is a concept of a macro in SQL Server, and if lists reading is possible in a macro?

For instance, if I have a bunch of dates I want to input in a list, like below (consider the below 4 macro variables of Start_Date, End_Date, Prev_Date and Table_Name)

Start_date=[1/1/2020, 2/1/2020, 3/1/2020]
End_date=[1/31/2020, 2/29/2020, 3/31/2020] 
Prev_Date=[12/31/2019,1/31/2020,2/29/2020]
Table_Name=[P1,P2,P3]

--and have a SQL script which runs like below

select a.metric1 as &Table_Name,
       b.metric2,
       c.metric3 
into &Table_Name(i)
from 
(
select metric1 from table
where Date between &Start_Date(i) & &End_Date(i)
)a
inner join 
(
select metric2 from tableX
where date<=&End_Date(i)
)b
on a.key=b.key
inner join 
(
select metric3 from tableX
where date<=&Prev_Date(i)
)c
on a.key=c.key;

So at the end of this code, we'd have 3 tables P1,P2,P3 create which I will then union together. Essentially, P1 is for between Start_Date 1/1/2020 & End Date 1/31/2020 and Prev_Date 12/31/2019 and so on...

I have &Prev_Date(i) as some form of a counter of reading into the elements of 3 macro variable lists.

Dale K
  • 25,246
  • 15
  • 42
  • 71
kaos1511
  • 113
  • 4
  • Personally I would use a table variable to store a collection of values like that. But no there is macro as you describe, T-SQL is primarily set based with a few procedural constructs. – Dale K May 01 '20 at 12:20
  • Thanks Dale, how would i use a table variable in this context? – kaos1511 May 01 '20 at 12:59
  • How are you going to provide data? is that in the script itself? Do you need to automate this, like passing parameterized data from outside and get the results returned? – Srinika Pinnaduwage May 01 '20 at 13:34
  • A quick google shows [this](https://stackoverflow.com/questions/1578198/can-i-loop-through-a-table-variable-in-t-sql) – Dale K May 01 '20 at 22:46

0 Answers0