CODEWITHSUNDEEP
sqlsql-serversql-view
DoIt
DoIt

Reputation: 3448

Check if a sql view exists and then create it dynamically before altering it

I am trying check if a SQL Server view exists and if it doesn't exist, create a view with dynamic script and then alter it.

I am using the following script

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[test]') AND OBJECTPROPERTY(id,N'IsView') = 1)
BEGIN
    EXEC dbo.sp_executesql @statement = N' CREATE VIEW [dbo].[test] AS '
END
GO

ALTER VIEW [dbo].[test]
---
---

The above script throws this error

Msg 102, Level 15, State 1, Procedure test, Line 1
Incorrect syntax near 'AS'.

May I know the correct way to do it?

Upvotes: 1

Views: 1159

Answers (2)

Tom H
Tom H

Reputation: 47402

You have no actual view definition in your code. That being said, why not just drop the view if it does exist and then your code can just always create the view instead of trying to do an ALTER?

Upvotes: 0

Chris Schiffhauer
Chris Schiffhauer

Reputation: 17310

The CREATE statement is incomplete. You can create the procedure by editing it to the following.

CREATE VIEW [dbo].[test] AS SELECT 1 'foo'

Once it is created, you can move on to altering it.

Edit: Fully escaped it will look like this:

EXEC dbo.sp_executesql @statement = N' CREATE VIEW [dbo].[test1] AS SELECT 1 ''foo'' '

Upvotes: 3

Related Questions