Why Can't SQL Server Alter a View in a Stored Procedure

Why can't SQL Server alter a view in a stored procedure?

I think the answers are:

1. MS want to prevent DDL being run from within procedures.
2. The code within the exec statement is not treated as part of the procedure - so it is not subject to the same restrictions as the procedure.
3. No.

An alternative approach might be to have a separate table (called something like swing_table) with either 1 or 0 records to indicate whether the view should query the production or other (backup?) table respectively - something like:

create view viewname as
select {field list}
from production_table
cross join swing_table
union all
select {field list}
from backup_table
where (select count(*) from swing_table) = 0

- then TRUNCATE swing_table within the procedure when you want to, erm, swing the table - since TRUNCATE is not a transactional command, it should execute immediately.

SQL unable to alter view having DECLARE statement

You cannot Declare a variable inside a View

Actually you don't need to use While loop for this. Use a tally table trick to generate dates much efficient than the while loop approach

ALTER VIEW [dbo].[Win.v_TodayMin365]
AS
  WITH E1(N)
       AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1), --10E+1 or 10 rows
       E2(N)
       AS (SELECT 1 FROM E1 a,E1 b), --10E+2 or 100 rows
       E4(N)
       AS (SELECT 1 FROM E2 a,E2 b), --10E+4 or 10,000 rows max
       calendar
       AS (SELECT Dateadd(dd, Row_number()OVER(ORDER BY n), Dateadd(yy, -1, Cast(Getdate() + 1 AS DATE))) AS dates
           FROM   E4 l)
  SELECT *
  FROM   calendar
  WHERE  dates <= Cast(Getdate() AS DATE) 

This even can be converted to Table valued function or a Stored Procedure.

SQL Server: trying to create view inside a stored procedure

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] 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

T-SQL: Alter View

A view only fetches the data from the table as per the query.So you cannot change the datatype of the view. you have to change it in table.

SQL Server 2008 R2: 'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name

It is really simple: you need to change the database

USE DB2
GO

ALTER VIEW View1
    ...

How to make CREATE OR REPLACE VIEW work in SQL Server?

Edit: Although this question has been marked as a duplicate, it has still been getting attention. The answer provided by @JaKXz is correct and should be the accepted answer.

---

You'll need to check for the existence of the view. Then do a CREATE VIEW or ALTER VIEW depending on the result.

IF OBJECT_ID('dbo.data_VVVV') IS NULL
BEGIN
    CREATE VIEW dbo.data_VVVV
    AS
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
END
ELSE
    ALTER VIEW dbo.data_VVVV
    AS
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
BEGIN
END

Can't use IF for create or alter view depending on view's existense

Because ALTER/CREATE commands can't be within BEGIN/END blocks. You need to test for existence and the drop it before doing a create

IF Object_ID('TestView') IS NOT NULL
    DROP VIEW TestView

GO

CREATE VIEW TestView
   as
   . . .

GO

If you are woried about the permissions being lost you can script the GRANT statements as well and re-run those at the end.

You could wrap the create/alter into a string and do an EXEC - that might get ugly for large views

DECLARE @SQL as varchar(4000)

-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE' 

IF Object_ID('TestView') IS NULL
    SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE    
    SET @SQL = 'ALTER VIEW TestView AS ' + @SQL

EXEC(@SQL)

SQL Server Invalid version: 15 (Microsoft.SqlServer.Smo)

Using SSMS version 18.0 instead of 17.0 seems to be working.

You can download it from https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

---

Related Topics