SQL 创建视图 - 声明一个变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40809271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create View - Declare a variable
提问by Ethel Patrick
I am creating a view that is using that STUFFfunction. I want to put the result of STUFFin 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 BEGINbefore it. I have tried putting it after the SELECTword. But nothing seems to work and I cannot find a solution in my search. I am using SQL Server 2012
我正在创建一个使用该STUFF函数的视图。我想将 的结果STUFF放在一个变量中以供我查看。我遇到的问题是声明我的变量。它给了我消息“'DECLARE'附近的语法不正确。期待'('或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
回答by James Casey
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 and keep it as a view.
编辑 - 您也可以将某些内容放入 CTE 并将其保留为视图。
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)
回答by Ahmed Saeed
Try put the condition subquery directly inside the the view select statement. you may CAST the XML to VARCHAR(20).
尝试将条件子查询直接放在视图选择语句中。您可以将 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
回答by Oleg Melnikov
Here is a sample query that uses CTE to nicely emulate internal variable construction, as described by James Casey. You can test-run it in your version of SQL Server.
这是一个示例查询，它使用 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
yielding output:
产生输出：
status name
12 dbo
0 db_accessadmin
0 db_securityadmin
0 db_ddladmin
also via JOIN
也通过 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
也通过 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
