"Simulate" Variables in a View

Question

Background

Once again, I am working in a very restricted T-SQL environment, an application where one may only define the "body" of a VIEW: presumably the ... in

CREATE VIEW My_View AS ...

under the hood. The ... must be written (as SQL) within its own text field in the application's GUI, which imposes certain inflexibilities described under Note.

Here is my @@VERSION:

Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64)
    Jan 27 2023 16:44:09  
    Copyright (C) 2019 Microsoft Corporation     
    Web Edition (64-bit) on Linux (Amazon Linux 2) <X64>

Note

The body (...) is limited to a single SQL statement! Furthermore, stored PROCEDUREs are out of play.

Goal

I wish to "simulate" the presence of "local" variables, so certain criteria can be dynamically updated, rather than hardcoded as literals throughout the query. Specifically, I want these "variables" to be at the top, so they can be easily adjusted.

Issue

Unfortunately, variables of the form @x are unavailable in views.

Approach

I have postulated this approach, with a vars CTE whose "fields" are the variables. Their values can be accessed as a subquery of the form (SELECT x FROM vars):

/*********************
** Variable Scalars **
**********************/
WITH vars AS (SELECT
    'Greg'    AS c_name,  -- A client named "Greg".
    GETDATE() AS t_date   -- Transactions occurring today.
    
    
    
/**********
** Query **
**********/
) SELECT
    *
FROM
    Transactions
WHERE
--                     |-------- 'Greg' -------|
    Client_Name      = (SELECT c_name FROM vars) AND
    Transaction_Date = (SELECT t_date FROM vars)
--                     |--------- Today -------|

I further suggest that variable lists of values can be specified as further CTEs, derived via STRING_SPLIT():

/*********************
** Variable Scalars **
**********************/
WITH vars AS (SELECT
    'Greg' AS c_name
    
    
    
/******************
** Variable List **
*******************/
), vars_dates AS (SELECT DISTINCT CAST([Value] AS DATE) AS vals FROM STRING_SPLIT('
    2023-06-28
    2023-06-01
    2023-01-01
', Char(10)) WHERE [Value] <> ''



/**********
** Query **
**********/
) SELECT
    *
FROM
    Transactions
WHERE
--                      |----------- 'Greg' ----------|
    Client_Name      =  (SELECT c_name FROM vars      ) AND
    Transaction_Date IN (SELECT vals   FROM vars_dates)
--                      |-----------------------------|
--                         {1/1/23, 6/1/23, 6/28/23}

Notice now the values in vars_dates are listed each on a separate line, so the user need not worry about punctuation (commas, etc.) clashing with any delimiter.

Question

So far, I have been able to call (SELECT x FROM vars) wherever I need the value of x. Indeed, both vars and vars_dates have worked perfectly throughout my use cases.

But are there any situations where these expressions will not work, in place of literal values and lists? More generally, what are the limitations of this approach?

Answer 1

You just want to define some values once at the top and then use them in repeated points throughout the view so you can change them in one place in the future.

Given the constraints you are working with the approach you have seems fine (if these values are not required to be shared in other views).

It is basically a more locally scoped version of the "Pseudo-Constant" idea here and at least for scalar values SQL Server should be able to use the underlying literal value for cardinality estimation (so better than an actual TSQL @variable in that respect).

For the scalar values rather than needing sub queries such as SELECT c_name FROM vars I'd be minded to just CROSS JOIN the single row vars so then the columns are available for easier use. See this example here:

-- An example table, in place of 'Transactions'.
CREATE TABLE #T(X INT);

INSERT INTO #T VALUES (1),(2),(2),(3),(3),(3);


-- The CROSS technique itself.
WITH vars AS 
(
SELECT 3 AS foo
)
SELECT #T.*
FROM vars 
CROSS JOIN #T 
WHERE X = vars.foo


-- Cleanup.
DROP TABLE #T

The below demonstrates the cardinality estimation was done using the value 3 and it correctly estimates 3 rows.

For the lists I'd likely use a Table Value Constructor rather than string splitting. It should allow SQL Server to see the number of rows in the lists easier (and maybe have other benefits for cardinality estimation if it looks at the actual values in these).

-- ...

/******************
** Variable List **
******************/
), vars_dates AS (SELECT vals FROM (VALUES
    (CAST('2023-06-28' AS date)), /*So the column from the constant scan is typed as date*/
    ('2023-06-01'),
    ('2023-01-01')
) tbl(vals)

-- ...

Unlike an actual variable this approach won't guarantee that the value will stay the same throughout the query if you use a non deterministic function but you don't have any way of actually guaranteeing that with your current constraints.