SQL Server: trying to create a view inside a stored procedure
I am trying to create my view inside a stored procedure, but I ran into an error.
My code:
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 the following errors:
Incorrect syntax near 'go'
"CREATE VIEW" must be the first statement in the query batch.
The maximum level of a stored procedure, function, trigger, or nesting display level has been exceeded (limit 32).
+3
source to share
1 answer
Remove "Go" as @mark_s correctly mentioned that it is not a SQL keyword that is executable in EXEC.
I created the procedure below to change the view in the same way as you do. Except that instead of using "Go", I use 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] 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
+3
source to share