Can i use DECLARE within a view -sqlserver or CTE?-CodePudding

I tried various approaches to get the view created with the dynamic variable at the time of creation of the view.

DECLARE @Test VARCHAR(64)='ABC'; // <==== attempt

SELECT TOP 10
    U.ID
    ,X.NAME AS yyyy
    ,X.E AS zzzz
FROM TABLE_A U
JOIN TABLE_B X ON U.ID=X.ID
WHERE U.STATUS='HELLO'
**AND X.NAME=@Test**
ORDER BY U.ID DESC

I am getting an error even if i try to use CTE

CREATE VIEW TESTVIEW AS
WITH Tests AS  (DECLARE @Test VARCHAR(64)='ABC')
    SELECT TOP 10
        U.ID
        ,X.NAME AS yyyy
        ,X.E AS zzzz
    FROM TABLE_A U
    JOIN TABLE_B X ON U.ID=X.ID
    WHERE U.STATUS='HELLO'
    **AND X.NAME=Tests**
    ORDER BY U.ID DESC

CodePudding user response：

You cannot declare a variable in a view. Why would you not create the view and then SELECT from the view using the string in the WHERE clause?

CREATE VIEW TESTVIEW AS
    SELECT TOP 10 -- Wouldn't recommend hardcoding this in a view
        U.ID
        ,X.NAME AS yyyy
        ,X.E AS zzzz
        ,X.NAME 
    FROM TABLE_A U
    JOIN TABLE_B X ON U.ID=X.ID
    WHERE U.STATUS='HELLO';

SELECT
   *
FROM TESTVIEW
WHERE NAME = 'ABC';

You could emulate it within the view with a CTE like:

CREATE VIEW TESTVIEW AS
    
WITH CTE AS (
   SELECT
       'ABC' as var
)

SELECT TOP 10 --Still wouldn't recommend hardcoding this in a view
        U.ID
        ,X.NAME AS yyyy
        ,X.E AS zzzz
        ,X.NAME 
    FROM TABLE_A U
    JOIN TABLE_B X ON U.ID=X.ID
    WHERE U.STATUS='HELLO'
    AND X.NAME = (SELECT var FROM cte);

SELECT * FROM TESTVIEW;