I have created a script that is part of an installation upgrade. This script needs to upgrade MYTABLE only if MYTABLE exists. I added a return statement after checking if it exists, but that doesn't seem to work. Because of 2008 rules for creating a view, I can't seem to enclose everything that follows in an if statement, nor can I figure out how to use a GOTO statement. Both seem to be rendered useless in this scenario. I only want to execute everything after RETURN if MYTABLE exists. How do I do that? The current code executes (and fails) when MYTABLE does not exists.
USE [MYDB]
GO
-- Only update MYTABLE if it exists.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MYTABLE]') AND type in (N'U'))
RETURN
ALTER TABLE [dbo].[MYTABLE] ADD [MYFIELD] [varchar](10) NULL
GO
CREATE NONCLUSTERED INDEX [IDX_MYTABLE_MYFIELD] ON [dbo].[MYTABLE]
(
[MYFIELD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MYVIEW]'))
DROP VIEW [dbo].[MYVIEW]
GO
-- Add new view
CREATE VIEW [dbo].[MYVIEW]
WITH SCHEMABINDING
AS
SELECT
MYFIELD
FROM dbo.MYTABLE
GO
--Add new Stored Procedures
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MYSP1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MYSP1]
GO
CREATE PROCEDURE [dbo].[MYSP1]
@MYFIELD varchar(10)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM MYVIEW
WHERE MYFIELD = @MYFIELD
END
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MYSP2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MYSP2]
GO
CREATE PROCEDURE [dbo].[MYSP2]
@MYFIELD varchar(9)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON;
--DO STUFF
END
go
Our community of experts have been thoroughly vetted for their expertise and industry experience.