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 procCreateIndexedView
AS
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'vwDemo1Only'
AND TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = 'dbo')
DROP VIEW dbo.vwDemo1Only
CREATE VIEW dbo.vwDemo1Only
WITH SCHEMABINDING
AS
SELECT Col1, Col2, Col3, Col5
FROM dbo.tblDemo
WHERE Col4 = 1
CREATE 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 9
Incorrect syntax near the keyword 'VIEW'.
Msg 319, Level 15, State 1, Procedure procCreateIndexedViews, Line 10
Incorrect 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 procCreateIndexedView
AS
IF 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)
Technorati tags:
dynamic SQL,
T-SQL