创建视图 - 声明一个变量

Question

我正在创建一个使用STUFF函数的视图。我想将STUFF的结果存储在一个变量中以供我的视图使用。然而，我无法声明我的变量。系统提示："Incorrect Syntax near 'DECLARE'. Expecting '(' or SELECT." 我已经放置了'('符号，还尝试在其前后加上BEGIN和SELECT，但仍然无法解决问题，也未能在搜索中找到解决方案。我使用的是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

- Ethel Patrick

---

在视图中无法声明变量。请使用用户存储过程或函数。 - Eric

这回答您的问题吗？SQL视图-没有变量？ - Jim G.

Answer 1

你不能在视图中声明变量。您是否可以将其转换为函数或存储过程？ 编辑 - 你也可以将一些内容放入CTE(公共表达式)中，并将其保留为视图。 例如：

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)

- James Casey

---

它让我执行了 with conditions as ( .. ) 和 left join，但是如何在我的视图中为 conditions 创建一个列呢？因为它不喜欢只有 conditions。 - Ethel Patrick

"conditions"将被视为一个表，所以它将被命名为conditions.ConditionsReasonsID。 - James Casey

@Ahmed的答案也很好 - 使用子查询而不是CTE。 - James Casey

我能够使用ConditionsReasonID。感谢您与我合作。 - Ethel Patrick

使用CTE的缺点是生成的视图无法索引。 - kpkpkp

Answer 2

这里是使用CTE（公共表达式）的示例查询，可以很好地模拟James Casey描述的内部变量构建。您可以在您的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

产生输出:

status  name
12      dbo
0       db_accessadmin
0       db_securityadmin
0       db_ddladmin

也可以通过 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

还可以通过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

- Oleg Melnikov

Answer 3

或者使用CTE（公共表达式）作为子查询，像这样：

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]

- Bettelbursche

Answer 4

尝试将条件子查询直接放置在视图选择语句中。您可以将XML转换为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

- Ahmed Saeed

---

当我这样做时，我收到了错误消息：“仅在子查询未引入EXISTS时才可以在选择列表中指定一个表达式。” 我认为这是由于“STUFF”函数引起的。 - Ethel Patrick