工作创建视图在内部给出语法错误(如果不存在)

Question

我有一个简单的创建视图脚本，用于统计报告，它本身就可以工作：

CREATE VIEW [dbo].[Report888View] AS
SELECT TOP 5000 pv.[AgentName] AS 'Label',
COUNT(*) AS 'NumData1'
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode='888' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY NumData1 DESC;

当我在SQL Server 2016上进行开发时，不能保证在客户安装上，因此我无法使用"创建"或"更改"。

所以我想我应该把它放在一个标准中，如果不存在：

IF NOT EXISTS
(
SELECT [name]
FROM sys.views
WHERE [name] = 'Report888View'
)
BEGIN
CREATE VIEW [dbo].[Report888View] AS
SELECT TOP 5000 pv.[AgentName] AS 'Label',
COUNT(*) AS 'NumData1'
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode='888' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY NumData1 DESC;
END

(我已经尝试过有和没有开始-结束(

但是，此代码为我提供了"语法不正确：'创建视图'必须是批处理中唯一的语句。我看不出我做错了什么...

编辑：更正了"数字数据1" --> 数字数据1

Answer 1

如果视图存在并且需要使用 GO 语句分隔批处理，则应先删除视图

if exists(select 1 from sys.views where name='Report888View' and type='v')
drop view Report888View;
go
CREATE VIEW [dbo].[Report888View] AS
SELECT TOP 5000 pv.[AgentName] AS 'Label',
COUNT(*) AS 'NumData1'
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode='888' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY 'NumData1' DESC;

Answer 2

你可以试试这个

对于 SQL Server 2016+

CREATE OR ALTER VIEW dbo.Report888View AS
SELECT TOP 5000 pv.[AgentName] AS 'Label',
COUNT(*) AS 'NumData1'
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode='888' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY NumData1 DESC;

对于以下 SQL Server 2016

IF EXISTS ( SELECT * FROM SYS.VIEWS WHERE NAME = 'Report888View' )
BEGIN
DROP VIEW dbo.Report888View        
END

---- OR YOU MAY USE THIS TO DROP VIEW
IF (SELECT OBJECT_ID('dbo.Report888View') )  IS NOT NULL
BEGIN
DROP VIEW dbo.Report888View          
END
GO
CREATE VIEW dbo.Report888View AS
SELECT TOP 5000 pv.[AgentName] AS 'Label',
COUNT(*) AS 'NumData1'
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode='888' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY NumData1 DESC;

Answer 3

如果视图上存在，并且您不希望这些 lsot 9so 仅在视图确实不存在时才创建视图(，则需要使用"动态"语句，如下所示：

IF NOT EXISTS
(
SELECT [name]
FROM sys.views
WHERE [name] = 'Report888View'
)
BEGIN
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'
CREATE VIEW [dbo].[Report888View] AS
SELECT TOP 5000 pv.[AgentName] AS ''Label'',
COUNT(*) AS ''NumData1''
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode=''888'' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY NumData1 DESC;';
EXEC sp_executesql @SQL;
END

sp_executesql运行的 SQL 的范围将位于单独的批处理中，因此不会生成错误。另请注意，我"修复"了您的ORDER BY的问题。'NumData1和NumData1完全不同;一个是文字字符串，另一个是SELECT中定义的列的别名。

或者，如果要CREATE或ALTER视图，无论它是否存在，都可以使用表达式来更改关键字的值：

DECLARE @SQL nvarchar(MAX);
SET @SQL = CASE WHEN  EXISTS(SELECT [name] FROM sys.views WHERE [name] = N'Report888View') THEN N'ALTER' ELSE N'CREATE' END + N' VIEW [dbo].[Report888View] AS
SELECT TOP 5000 pv.[AgentName] AS ''Label'',
COUNT(*) AS ''NumData1''
FROM [FullmaktsnodenProcess].[dbo].[ProcessingView] pv          
JOIN [Fullmaktsnoden].[dbo].[POAs] p ON (pv.FullmaktskollenPOAId=p.FullmaktskollenPoaId)
JOIN [Fullmaktsnoden].[dbo].[ReceivedPOAs] rp ON (p.Id=rp.Id)
WHERE pv.ErrorCode=''888'' and pv.Status <> 9
GROUP BY pv.[AgentName]
ORDER BY NumData1 DESC;';
EXEC sp_executesql @SQL;

Answer 4

尝试这样的事情：

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[MyView]
AS
...
'