Sql Server: Trying To Create View Inside A Stored Procedure

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_Sale

Solution 1:

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

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.

create procedure [dbo].[CreateInvoiceView]
as 
begin
    Exec ('If object_ID(''invoices'',''V'') is not null 
            drop view invoices;')

    Exec ('
        create view [dbo].[Invoices] ASSELECT 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 ShipperNameFROM    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