CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-schema
Learner
Learner

Reputation: 1695

Views as Stored Procedures in Schema Change script

I have writing a Change script to update the schema in the Production DB. I have to drop a View and recreate them since one of the column in that view is removed now.

I'm planning to use, 

DECLARE @sql NVARCHAR(MAX);
SET @sql = '<View Definition>';

EXEC sp_executesql @sql;

If any, What are the pitfalls of using this approach ? What are the alternatives that you would suggest ?

I'm having the whole script under a transaction with a Try catch block. So when there is no error the script will be committed else rolled back.I tried to use CREATE VIEW Command as below, but I get error after introduction TRASACTION and TRY-CATCH BLOCKS.This reason why am going for Dynamic SQL statement - EXEC sp_executesql.

The error is "CREATE VIEW MUST BE THE ONLY STATEMENT IN THE BATCH"

IF EXISTS ( SELECT * FROM sys.views WHERE name = 'VwViewName')
BEGIN 
DROP VIEW VwViewName
END

IF NOT EXISTS ( SELECT * FROM sys.views WHERE name = 'VwViewName')
BEGIN 
     CREATE VIEW VwViewName
    <VIEW DEFINITION>
END

Upvotes: 1

Views: 282

Answers (1)

Luis LL
Luis LL

Reputation: 2993

Since you are running schema update, you should comply minimal things like

  • make it repeatable
  • have it in your versioning system
  • and test it in your testing environment before running in production.

In order to make it re-runable it's common practice to check if the object exists and drop it, and then to create it again.

Dynamic SQL will not add any functionality that the IF clause has.

For example: 

IF EXISTS(SELECT * FROM sys.views WHERE name = 'MyView')
BEGIN
    DROP VIEW [MyView]
END
GO
CREATE VIEW MyView AS 
    SELECT [Columns list ]
    FROM [MyTable]
GO

The only case that I know which justifies dynamic SQL is if you want to preserve grant / deny, in such case you can use following syntax.

IF NOT EXISTS(SELECT * FROM sys.views WHERE name = 'MyView')
BEGIN
    EXEC sp_executesql N'CREATE VIEW MyView AS SELECT 1 AS Dummy'
END
GO
ALTER VIEW MyView AS 
    SELECT [Columns list ]
    FROM [MyTable]

go
GRANT ....

I wouldn't create a Dynamic SQL to fill the few times that I have to repeat the names in any of the scripts below. IMHO the deployment script should be as plain as possible.

Upvotes: 2

Related Questions