CODEWITHSUNDEEP
sql-servert-sql
Scott Weinstein
Scott Weinstein

Reputation: 19117

SQL Syntax for testing objects before creating views & functions

I'm trying to figure out the syntax for creating a view (or function) but only if a dependent CLR assembly exits.

I've tried both

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyCLRAssembly') 
begin
create view dbo.MyView as select GETDATE() as C1
end

and 

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyCLRAssembly') 
create view dbo.MyView as select GETDATE() as C1
go

Neither work. I get

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'view'.

How can this be done?

Upvotes: 1

Views: 125

Answers (2)

Sparky
Sparky

Reputation: 15105

Here is workaround

if object_id('MyView','V') is null
    exec ( 'create view dbo.MyView as select GETDATE() as C1' )

Upvotes: 1

Andrew Bezzub
Andrew Bezzub

Reputation: 16032

This is strange for me, but what I see in MSDN is "CREATE VIEW must be the first statement in a query batch." So it looks like you cannot create views inside IF statement.

Upvotes: 1

Related Questions