CODEWITHSUNDEEP
sqlsql-server-2005ssms
wilu
wilu

Reputation: 559

Create view and use it in a single SQL script

Simple thing ... i thought. Create a view and use it later in the same SQL script. Let's say we have a script as follows:

CREATE VIEW someView AS (...)
DROP VIEW someView

If I try to parse it SQL Management complaints there's an error around DROP. If I execute them separately (create first, then drop) they work both fine. Is there any way to create a view and use it in a single SQL script? I could wrap further statements in string an then EXEC it but it's a bit inconvenient.

Code example was fixed (missing VIEW)

More meaningful example:

create view TEST as (select name from spt_values where number=1);
drop view TEST

Is it possible to execute it at once? I got the error:

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

Running create statement separately and then dropping view works perfectly.

Upvotes: 0

Views: 2787

Answers (2)

Habibillah
Habibillah

Reputation: 28695

Separate your query with GO keyword like query bellow:

CREATE VIEW someView AS ()
GO

DROP VIEW someView
GO

Upvotes: 2

Colin 't Hart
Colin 't Hart

Reputation: 7729

Regardless of which particular DBMS you are using, you should create a script separating your SQL statements with ';'.

For example

CREATE VIEW someView as (...);

<<some other sql statements>>

DROP VIEW someView;

Upvotes: 1

Related Questions