How can i alter two views in one sql statement using SQL Developer

Transact-SQL
1

USE SOMEDATABASE
GO

ALTER VIEW MYVIEWA AS SELECT * FROM VIEWA
--GO

ALTER VIEW MYVIEWB AS SELECT * FROM MYVEIWB
GO

Msg 156, Level 15, State 1, Procedure MYVIEWA, Line 6 [Batch Start Line 3]
Incorrect syntax near the keyword 'ALTER'.

2

An ALTER VIEW must be in a batch by itself. So you would need the GO between the two ALTERs.

3

Thanks i will try again

4

Still not much luck, so i did an example and wonder if you can see the issue when dynamically running this.

EXECUTE [dbo].[SP_Test_Views] 'YOURDATABASENAME'

CREATE TABLE [dbo].[TABLEA](
[column1] nchar NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TABLEB](
[column1] nchar NULL
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[View_A] AS SELECT column1 FROM "CPERPSYS"."dbo".TABLEA
GO

CREATE VIEW [dbo].[View_B] AS SELECT column1 FROM "CPERPSYS"."dbo".TABLEB
GO

CREATE Procedure [dbo].[SP_Test_Views]

@DATABASE_NAME nvarchar(20)

AS

DECLARE

@SQL NVARCHAR(MAX)

DECLARE @CP_DATABASE_NAME varchar(max)

SET @CP_DATABASE_NAME = @DATABASE_NAME

---Linked Views
SET @SQL = 'USE ' + @DATABASE_NAME + CHAR(13) + 'GO'
PRINT @SQL

SET @SQL = 'ALTER VIEW ' + 'dbo' + '.View_A AS SELECT column1 FROM ' + '"' + @CP_DATABASE_NAME + '"' + '.' + '"' + 'dbo' + '"' + '.TABLEA' + CHAR(13) + 'GO'
PRINT @SQL

SET @SQL = 'ALTER VIEW ' + 'dbo' + '.View_B AS SELECT column1 FROM ' + '"' + @CP_DATABASE_NAME + '"' + '.' + '"' + 'dbo' + '"' + '.TABLEB' + CHAR(13) + 'GO'
PRINT @SQL

5

hi

hope this helps

i got the same issues as you

but i thought of a work around doing each alter individually

CREATE PROCEDURE [dbo].[Sp_test_views] @DATABASE_NAME NVARCHAR(20)
AS
    DECLARE @SQL NVARCHAR(max)
    DECLARE @CP_DATABASE_NAME VARCHAR(max)

    SET @CP_DATABASE_NAME = @DATABASE_NAME
    ---Linked Views
    SET @SQL = 'USE ' + @DATABASE_NAME + Char(13) + 'GO'
    SET @SQL = 'ALTER VIEW ' + 'dbo'
               + '.View_A AS SELECT column1 FROM ' + '"'
               + @CP_DATABASE_NAME + '"' + '.' + '"' + 'dbo' + '"'
               + '.TABLEA' + Char(13) + 'GO'

    EXEC(@SQL)
	
    SET @SQL = 'USE ' + @DATABASE_NAME + Char(13) + 'GO'
    SET @SQL = 'ALTER VIEW ' + 'dbo'
               + '.View_B AS SELECT column1 FROM ' + '"'
               + @CP_DATABASE_NAME + '"' + '.' + '"' + 'dbo' + '"'
               + '.TABLEB' + Char(13) + 'GO'

    EXEC(@SQL)
1 Like