Create View - Declare a variable

Question

I am creating a view that is using that STUFF function. I want to put the result of STUFF in a variable for my view. The problem I am having is declaring my variable. It gives me the message "Incorrect Syntax near 'DECLARE'. Expecting '(' or SELECT." I already have the '(' in there. I have tried putting a BEGIN before it. I have tried putting it after the SELECT word. But nothing seems to work and I cannot find a solution in my search. I am using SQL Server 2012

CREATE VIEW [AQB_OB].[GISREQUESTEDBURNS] 
AS

(DECLARE @CONDITIONS AS varchar(20)
SET @CONDITIONS = (SELECT DISTINCT BD.[RequestedBurnsID]
,[ConditionsReasonsID] = STUFF((SELECT ', ' + CONVERT(VARCHAR (20),[ConditionsReasonsID]) FROM [AQB_OB].[BurnDecisions] WHERE [RequestedBurnsID]= BD.[RequestedBurnsID] ORDER BY [RequestedBurnsID] ASC
FOR XML PATH ('')) , 1 , 1, '') FROM
[AQB_OB].[BurnDecisions] BD)

SELECT RB.[RequestedBurnsID] AS REQUESTEDBURNID
  ,BUY.[BurnYear] AS BURNYEAR
  ,CY.[CurrentYear] AS CURRENTYEAR
  ,RB.[BurnSitesID] AS BURNSITESID
  ,[BurnerID] AS BURNERID
  ,[Contact] AS CONTACT
  ,[BurnDecision] AS BURNDECISION
  ,RB.[Comment] AS COMMENT
  ,@CONDITIONS AS CONDITIONS
FROM [AQB_MON].[AQB_OB].[RequestedBurns] RB
LEFT join AQB_MON.[AQB_OB].[PileDryness] PD on RB.[PileDrynessID] = PD.[PileDrynessID]
inner join AQB_MON.[AQB_OB].[BurnYear] BUY on BUY.BurnYearID = BP.BurnYearID
inner join AQB_MON.[AQB_OB].[CurrentYear] CY on CY.CurrentYearID = BUY.CurrentYearID

GO

You can't declare variables in views. User stored procedure or function instead. — Eric, Nov 25 '16 at 16:37
Does this answer your question? [SQL Views - no variables?](https://stackoverflow.com/questions/6114826/sql-views-no-variables) — Jim G., Oct 29 '20 at 15:59

score 30 · Accepted Answer · edited Apr 27 '21 at 17:24

30

You can't declare variables in a view. Could you make it into a function or stored procedure?

Edit - you might also be able to put something into a CTE (Common Table Expression) and keep it as a view.

e.g.

WITH conditions as 
(
 ... do the STUFF here
)
SELECT blah
FROM blah
INNER JOIN conditions
(or CROSS JOIN conditions if its just one row, I can't quite decipher what your data is like)

edited Apr 27 '21 at 17:24

Abel

56,041
24
146
247

answered Nov 25 '16 at 16:34

James Casey

2,447
1
11
19

It let me perform the `with conditions as ( .. )` and an `left join` however how do I make a column for `conditions` in my view as it does not like just `conditions` – Ethel Patrick Nov 25 '16 at 16:50
1

"conditions" will just be treated like a table, so it will be conditions.ConditionsReasonsID. – James Casey Nov 25 '16 at 17:09
@Ahmed's answer is probably good too - a subquery rather than a cte – James Casey Nov 25 '16 at 17:10
I was able to use ConditionsReasonID. Thank-you for working with me. – Ethel Patrick Nov 25 '16 at 17:22
The downside to using the CTE is that the resulting view cannot be indexed – kpkpkp Apr 29 '21 at 17:36

score 24 · Answer 2 · edited Apr 27 '21 at 17:24

Here is a sample query that uses a CTE (Common Table Expression) to nicely emulate internal variable construction, as described by James Casey. You can test-run it in your version of SQL Server.

CREATE VIEW vwImportant_Users AS
WITH params AS (
    SELECT 
    varType='%Admin%', 
    varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers, params
    WHERE status > varMinStatus OR name LIKE varType

SELECT * FROM vwImportant_Users

yielding output:

status  name
12      dbo
0       db_accessadmin
0       db_securityadmin
0       db_ddladmin

also via JOIN

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers INNER JOIN params ON 1=1
    WHERE status > varMinStatus OR name LIKE varType

also via CROSS APPLY

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers CROSS APPLY params
    WHERE status > varMinStatus OR name LIKE varType

score 5 · Answer 3 · answered Apr 07 '21 at 09:57

Or use a CTE (common table expression) as subselect like:

WITH CTE_Time(Clock)
AS(
    SELECT 11 AS [Clock] -- set var
)

SELECT 
    DATEPART(HOUR, GETDATE()) AS 'actual hour',
    CASE
        WHEN DATEPART(HOUR, GETDATE()) >= (SELECT [Clock] FROM CTE_Time) THEN 'after'
        ELSE 'before'
    END AS [Data]

score 4 · Answer 4 · answered Nov 25 '16 at 17:04

Try put the condition subquery directly inside the the view select statement. you may CAST the XML to VARCHAR(20).

CREATE VIEW [AQB_OB].[GISREQUESTEDBURNS] 
AS    
SELECT RB.[RequestedBurnsID] AS REQUESTEDBURNID
  ,BUY.[BurnYear] AS BURNYEAR
  ,CY.[CurrentYear] AS CURRENTYEAR
  ,RB.[BurnSitesID] AS BURNSITESID
  ,[BurnerID] AS BURNERID
  ,[Contact] AS CONTACT
  ,[BurnDecision] AS BURNDECISION
  ,RB.[Comment] AS COMMENT,

 (
     SELECT DISTINCT BD.[RequestedBurnsID],
             [ConditionsReasonsID] = STUFF((SELECT ', ' + CONVERT(VARCHAR (20),     [ConditionsReasonsID]) FROM [AQB_OB].[BurnDecisions] 
     WHERE [RequestedBurnsID]= BD.[RequestedBurnsID] ORDER BY [RequestedBurnsID] ASC
    FOR XML PATH ('')) , 1 , 1, '') FROM
    [AQB_OB].[BurnDecisions] BD
 ) AS CONDITIONS

FROM [AQB_MON].[AQB_OB].[RequestedBurns] RB
LEFT join AQB_MON.[AQB_OB].[PileDryness] PD on RB.[PileDrynessID] = PD.[PileDrynessID]
inner join AQB_MON.[AQB_OB].[BurnYear] BUY on BUY.BurnYearID = BP.BurnYearID
inner join AQB_MON.[AQB_OB].[CurrentYear] CY on CY.CurrentYearID = BUY.CurrentYearID

When I do this I received the error `Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.`. Which I think is due to the `STUFF` function. — Ethel Patrick, Nov 25 '16 at 17:12

Create View - Declare a variable

4 Answers4

Linked