asked
Stored procedure to include multiple create view statements
Hello, I am trying to use a Stored Procedure to include multiple Create View but I have no luck getting to work. Please See below for a sample. Create procedure MyCreateView AS IF OBJECT_ID('[Table1]', 'V') IS NOT NULL DROP VIEW [Table1]; CREATE VIEW [Table1] AS SELECT ABC FROM [Table2] IF OBJECT_ID('[Table2]', 'V') IS NOT NULL DROP VIEW [Table2]; CREATE VIEW [Table2] AS SELECT DEF FROM [TABLE3] Thank you for your help.
CREATE VIEW must be the first statement in a batch. If you need to use it in a procedure like you are doing, you'll need to wrap it around EXEC/SP_EXECUTESQL MSDN reference:
https://msdn.microsoft.com/en-us/library/ms187956.aspx This compiles for me: CREATE PROCEDURE dbo.MyCreateView AS BEGIN IF OBJECT_ID('[Table1]', 'V') IS NOT NULL DROP VIEW [Table1] EXEC ('CREATE VIEW [Table1] AS SELECT * FROM sys.objects') IF OBJECT_ID('[Table2]', 'V') IS NOT NULL DROP VIEW [Table2] DECLARE @stmnt nvarchar(50) = 'CREATE VIEW [Table2] AS SELECT * FROM sys.tables' EXEC SP_EXECUTESQL @stmnt END GO EXEC dbo.MyCreateView GO I showed both EXEC/SP_EXECUTESQL methods in the code above. Hope that helps!
16 People are following this question.
