SQL Server Books Online states there are a number of statements you can't include in a stored procedure, including CREATE VIEW. In most cases that wouldn't be a problem, as you can create the view using a script, unless... I need an indexed view on a table that is created by a stored procedure. At the time the script runs to create the objects (views, functions and stored procedures), I can't create a schemabound view on a non-existing table. I have to create the view after the table, but if I try in a stored procedure; error messages!
CREATE PROCEDURE procCreateIndexedViewASIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'vwDemo1Only' AND TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'dbo')DROP VIEW dbo.vwDemo1OnlyCREATE VIEW dbo.vwDemo1Only WITH SCHEMABINDING AS SELECT Col1, Col2, Col3, Col5 FROM dbo.tblDemo WHERE Col4 = 1CREATE UNIQUE CLUSTERED INDEX ix_Demo1_Col1Col2 ON dbo.vwDemo1Only(Col1, Col2)CREATE INDEX ix_Demo1_Col5 ON dbo.vwDemo1Only(Col5) Msg 156, Level 15, State 1, Procedure procCreateIndexedViews, Line 9Incorrect syntax near the keyword 'VIEW'.Msg 319, Level 15, State 1, Procedure procCreateIndexedViews, Line 10Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
CREATE PROCEDURE procCreateIndexedViewASIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'vwDemo1Only' AND TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'dbo')DROP VIEW dbo.vwDemo1OnlyCREATE VIEW dbo.vwDemo1Only WITH SCHEMABINDING AS SELECT Col1, Col2, Col3, Col5 FROM dbo.tblDemo WHERE Col4 = 1CREATE UNIQUE CLUSTERED INDEX ix_Demo1_Col1Col2 ON dbo.vwDemo1Only(Col1, Col2)CREATE INDEX ix_Demo1_Col5 ON dbo.vwDemo1Only(Col5)
Msg 156, Level 15, State 1, Procedure procCreateIndexedViews, Line 9Incorrect syntax near the keyword 'VIEW'.Msg 319, Level 15, State 1, Procedure procCreateIndexedViews, Line 10Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Fortunately, there is an escape: Dynamic SQL. Simply by encapsulating the creation of the view as dynamic SQL, the stored procedure is created without errors. Also, when executing the stored procedure the view is created as well (that is, if the table already exists )
CREATE PROCEDURE procCreateIndexedViewASIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'vwDemo1Only' AND TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'dbo')DROP VIEW dbo.vwDemo1Only-- Add next line to encapsulated view defenition as dynamic SQL. EXEC('CREATE VIEW dbo.vwDemo1Only WITH SCHEMABINDING AS SELECT Col1, Col2, Col3, Col5 FROM dbo.tblDemo WHERE Col4 = 1')-- Add previous line to stop encapsulation.CREATE UNIQUE CLUSTERED INDEX ix_Demo1_Col1Col2 ON dbo.vwDemo1Only(Col1, Col2)CREATE INDEX ix_Demo1_Col5 ON dbo.vwDemo1Only(Col5)