SQL Server:试图在存储过程中创建视图
问题描述:
我试图在存储过程中创建我的视图,但是我遇到了一个错误。SQL Server:试图在存储过程中创建视图
我的代码是:
alter PROCEDURE p.Azmoon1
AS
begin
EXEC ('IF OBJECT_ID (''r.r_Sales01_Requests__Duplicates'', ''V'') IS NOT NULL
DROP VIEW r.r_Sales01_Requests__Duplicates ;
go
create view r.r_Sales01_Requests__Duplicates (
CompanyID
,Branch
,Year
,VoucherType,VoucherNumber
,Date_Persian
,Row
) as
select
CompanyID
,Branch
,Year
,VoucherType,VoucherNumber
,Date_Persian
,Row
from t_SalesRequests
group by CompanyID,Branch,Year,VoucherType,VoucherNumber,Date_Persian,Row
having count(*)>1
go
')
end
当我打电话给我的程序如下图所示:
execute p.Azmoon1
我得到了这些错误:近
不正确的语法 '走'
“ CREATE VIEW'必须是查询批处理中的第一条语句。
超过最大存储过程,函数,触发器或视图嵌套级别(限制32)。
答
删除“Go”作为@mark_s正确地提到它不是在EXEC中可执行的SQL关键字。
我创建了下面的过程来修改视图,就像你有很多。除了使用'Go'外,我正在使用两个单独的EXEC语句。
create procedure [dbo].[CreateInvoiceView]
as
begin
Exec ('If object_ID(''invoices'',''V'') is not null
drop view invoices;')
Exec ('
create view [dbo].[Invoices] AS
SELECT Orders.ShipName as SHIP_Name, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, (FirstName + '' '' + LastName) AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName
FROM Shippers INNER JOIN
(Products INNER JOIN
(
(Employees INNER JOIN
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID)
ON Shippers.ShipperID = Orders.ShipVia
')
end
为什么要在存储过程中创建视图*没有任何意义.... if你需要一个存储过程中的视图 - 你不能只是使用CTE(公用表表达式)吗? – 2014-09-05 10:18:24
错误的原因是'GO'是**不是一个有效的SQL关键字 - 它是一个在SQL Server ** Management Studio **中可用的分隔符... – 2014-09-05 10:19:03
@marc_s我想在存储过程中创建视图,因为我有近2500多个视图来创建,并且我希望以后可以轻松更改它们的模式。 – 2014-09-05 10:24:09