创建视图 - 声明一个变量 [英] Create View - Declare a variable

问题描述

我正在创建一个使用 STUFF 函数的视图.我想将 STUFF 的结果放在一个变量中以供我查看.我遇到的问题是声明我的变量.它给了我消息'DECLARE'附近的语法不正确.期待'('或SELECT."我已经有'('在那里.我试过把一个BEGIN 在它之前.我尝试将它放在 SELECT 词之后.但似乎没有任何效果，我在搜索中找不到解决方案.我使用的是 SQL Server 2012

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 a view. Could you make it into a function or stored procedure?

编辑 - 您也可以将某些内容放入 CTE(通用表表达式) 并将其保留为视图.

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

例如

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)

这篇关于创建视图 - 声明一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！