SQL Server：尝试在存储过程中创建视图

I'm trying to create my view inside the stored procedure but I faced an error .

我正在尝试在存储过程中创建我的视图，但我遇到了错误。

My code is :

我的代码是：

    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

When I call my procedure like below :

当我打电话给我的程序如下：

execute p.Azmoon1

I got these errors:

我收到了这些错误：

Incorrect syntax near 'go'
'CREATE VIEW' must be the first statement in a query batch.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

'go''CREATE VIEW'附近的语法不正确必须是查询批处理中的第一个语句。超出最大存储过程，函数，触发器或视图嵌套级别（限制32）。

1 个解决方案

#1

Remove the "Go" as @mark_s rightly mentioned it is not a SQL keyword that is executable in EXEC.

删除“Go”为@mark_s，正确地提到它不是在EXEC中可执行的SQL关键字。

I created the below procedure to modify the view much like you have. Except that instead of using a 'Go", I am using two separate EXEC statements.

我创建了以下过程来修改视图，就像你一样。除了不使用“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

#1