Create view and use it in a single SQL script

Question

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.

SQLServer 2005 + Microsoft SQL Server Management Studio 9.00.5000.00 — wilu
– wilu, Commented Oct 22, 2012 at 7:36

Habibillah · Accepted Answer · 2012-10-22 07:49:46Z

2

Separate your query with GO keyword like query bellow:

CREATE VIEW someView AS ()
GO

DROP VIEW someView
GO

answered Oct 22, 2012 at 7:49

Habibillah

28.8k5 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Colin 't Hart · Accepted Answer · 2012-10-22 07:37:29Z

1

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;

answered Oct 22, 2012 at 7:37

Colin 't Hart

7,8084 gold badges34 silver badges53 bronze badges

4 Comments

wilu Over a year ago

That's right, nonetheless it does not solve the problem - how to use newly created view later, within the same script.

Colin 't Hart Over a year ago

Please post more of your script and the error you are getting.

wilu Over a year ago

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

Colin 't Hart Over a year ago

Please always update your original question so that the entire problem statement is in one place.

Collectives™ on Stack Overflow

Create view and use it in a single SQL script

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related