question

Allenb avatar image
Allenb 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.
sql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
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!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It works perfectly. Thanks for your help.
0 Likes 0 ·
Welcome! If that indeed did solve your issue, please make sure to mark it as the answer so that others know the question was answered. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.