Mulitple Create Views in Query Batch

I have a script that I am running from a Query Analyser that I want to put
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.

> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]