Creating A Table Of Variables In SQL

[intapiuser]

MySQL aficionados have likely leveraged its ability to declare variables. Unfortunately, this capability isn't supported by all flavors of SQL. For such situations, we can still achieve a similar result by creating a CTE of variables (for more information on CTEs - refer to the post here!)

Example:

with
  variables as (
    select
      2 as v1
      , 'data' as v2
      , ['2018-07-01':date] as v3
  )
select
  *
from
  purchases
  join variables on
    1 = 1

Notice how we have our variables readily accessible for each record. Now, feel free to calculate and plug in as needed!

Tip: Depending on your exact use case, you may opt to use a Parameterized SQL Snippet instead!