LuckyLucks
asked on
MS SQL server 2012 query
Hi:
I am looking to drop and recreate a view, if its exists, and Grant a role to it. I have the skeleton below that doesn't work, could you advise me of the correction?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
DROP VIEW [dbo].[myView];
CREATE VIEW [dbo].[myView]
AS
SELECT
COl1,
COl2
FROM Table T
GO
GRANT ROLE myROLE to [dbo].[myView]
I am looking to drop and recreate a view, if its exists, and Grant a role to it. I have the skeleton below that doesn't work, could you advise me of the correction?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
DROP VIEW [dbo].[myView];
CREATE VIEW [dbo].[myView]
AS
SELECT
COl1,
COl2
FROM Table T
GO
GRANT ROLE myROLE to [dbo].[myView]
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Correct. That way it drops only if it already exists, potentially avoiding a 'you tried to drop a view that does not exist' error.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
IF EXISTS (SELECT name FROM sys.views WHERE name='myView')
DROP VIEW [dbo].[myView]
GO
CREATE VIEW [dbo].[myView]
AS
blah
GO