IF NOT EXISTS problem
-
Why would the following code produce this error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'VIEW'.
The drop worked as it should. This problem happens elsewhere in my script
and I can't figure out why.
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwScans]'))
DROP VIEW [dbo].[vwScans]
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwScans]'))
CREATE VIEW vwScans
AS
SELECT coScanTypes.Category, coScanTypes.Type, coScanTypes.Description, Scans.DOS,
Scans.MRN, Scans.Path, Scans.ID, Scans.Comment, Scans.Deleted,
Scans.EMRApproved, Scans.EMRApprovedBy
FROM coScanTypes
INNER JOIN Scans
ON coScanTypes.Type = Scans.Type
WHERE (Scans.Deleted = 0)
OR (Scans.Deleted IS NULL)
GO
I've even tried this code and get the same error:
IF (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwScans]')) = 0
-
Unlike DROP VIEW a CREATE VIEW must be the first statement in a query batch so your second check won't work. Just try:
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwScans]'))DROP VIEW [dbo].[vwScans]
GO
CREATE VIEW vwScans
AS
SELECT coScanTypes.Category, coScanTypes.Type, coScanTypes.Description, Scans.DOS,
Scans.MRN, Scans.Path, Scans.ID, Scans.Comment, Scans.Deleted,
Scans.EMRApproved, Scans.EMRApprovedBy
FROM coScanTypes
INNER JOIN Scans
ON coScanTypes.Type = Scans.Type
WHERE (Scans.Deleted = 0)
OR (Scans.Deleted IS NULL)
GO
-
I think the problem is that the CREATE VIEW statement should be the first line in the batch. If you remove the second if statement (which is unnecessary anyway as you have already checked for the presence of the view and dropped it if it exists) the code should run fine.
Tom
Life: it twists and turns like a twisty turny thing
-
Thanks...
-
Can you guys help with this one, too?
This code:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ptLabResultsorg]') AND type in (N'U'))
BEGIN
-- Altering ptLabResultsorg
Print 'Altering ptLabResultsorg'
ALTER TABLE ptLabResultsorg ALTER COLUMN MRN varchar(25) NOT NULL
GO
-- Updating ptLabResultsorg
Print 'Updating ptLabResultsorg'
UPDATE ptLabResultsorg SET MRN = RTRIM(MRN)
GO
END
results in these errors on a DB that contains this table
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'NULL'.
Updating ptLabResultsorg
Msg 208, Level 16, State 1, Line 5
Invalid object name 'ptLabResultsorg'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'END'.
and these on a DB that does not contain this table:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'NULL'.
Updating ptLabResultsorg
(174834 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'END'.
-
ALTER TABLE is the same as CREATE VIEW in that it must be the first statement in a query batch. With this type of script you would probably be better off with a transaction and retrospective error checking rather than validating the existence of objects.
-
i think it could be the fact that you have GO statements in the IF block. The GO effectively ends the batch, so the BEGIN statement doesn't have a corresponding END (and likewise, the END statement doesn't have a starting BEGIN)
Tom
Life: it twists and turns like a twisty turny thing
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply