March 28, 2004 at 1:13 am
Im getting an error that says "Incorrect syntax near the keywork VIEW."
CREATE PROCEDURE sp
AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'TheView')
DROP VIEW TheView
CREATE VIEW TheView
AS
SELECT * FROM ......
select * from vwDeptRequestSummary
I will appreciate any help, Thanx!
AUXilliary COMmunication 1
March 28, 2004 at 9:43 am
Try wrapping the creat view statement in dynamic SQL this should work. It won't allow a create view statement in SPs see the BOL article "Programming Stored Procedures" for more details on CREATE statements not allowed. But DynamicSQL will work around.
DECLARE @sql VARCHAR(8000)
SET @sql = 'CREATE VIEW TheView
AS
SELECT * FROM ......'
EXECUTE(@SQL)
Also as a better measure do IF EXISTS this way
IF OBJECT_ID('TheView') IS NOT NULL
DROP VIEW TheView
March 28, 2004 at 3:52 pm
Many thanks to you Antares! And also for the tip on IF EXISTS alternative.
AUXilliary COMmunication 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply