|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Mulitple Create Views in Query Batchinto 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 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. HTH Jerry Show quote "tshad" <tscheider***@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" <jspi***@vestas-awt.com> wrote in message But if you can only create one View in a Batch, that would be a problem in a 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. 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 Show quote > > HTH > > Jerry > "tshad" <tscheider***@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 >> > > 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 Show quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:ee%23VbK8vFHA.3312@TK2MSFTNGP09.phx.gbl... > "Jerry Spivey" <jspi***@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" <tscheider***@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" <jspi***@vestas-awt.com> wrote in message That is what I am doing. The problem is that the Declares for the dates are 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? 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 Show quote > > Jerry > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:ee%23VbK8vFHA.3312@TK2MSFTNGP09.phx.gbl... >> "Jerry Spivey" <jspi***@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" <tscheider***@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" <jspi***@vestas-awt.com> wrote in message It doesn't. And I did give them an sql script. I was trying to see if 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? 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 Show quote > > Jerry > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:ee%23VbK8vFHA.3312@TK2MSFTNGP09.phx.gbl... >> "Jerry Spivey" <jspi***@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" <tscheider***@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 >>>> >>> >>> >> >> > > Look up CREATE SCHEMA in BOL.
Show quote "tshad" <tscheider***@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 > 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. "--CELKO--" <jcelko***@earthlink.net> wrote in message Why not?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. 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. > You are right. That is the way I think. So shoot me. :)> 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. 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. Thinkabout it >> I am creating multiple Views that I am using temporarily << NO, NO, NO!! You create VIEWs because they have meaning as dataelements 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! >> You are right. That is the way I think. So shoot me. :) << 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! >> 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. << VIEWs are good, but they are part of a schema design and need to beplanned 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] On 23 Sep 2005 20:51:38 -0700, --CELKO-- wrote:
>>> Why not? << Hi Joe,> >VIEWs can be built on VIEWs; they have to be created in order. Think >about it You can have CREATE TABLE, some INSERT statements, a few SELECT queries and a DROP TABLE in one batch. They, too, have to be executed in order. Think about it. Batches are not tables, statements are not rows, reserved words are not columns. You are stuck in a late 20th century mindset, thinking that everything is set-based. Unlike tables, batches DO have an implied ordering. <g> (from an earlier message in this thread): > I happen to know the query Tom's talking about, because I helped him>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. write it. I advised him to create some views, because each of these views had to be included about ten times in the full query. Do you really prefer to copy and paste ten exact equal copies of the same derived table expression over creating a temporary view? Think about the maintenance nightmare if you discover an error in the derived table. Or if your data is wrong, and you spent hours researching until you finally see a teeny tiny typo in the eigth repetition of the derived table. With SQL Server 2005, I'd have advised Tom to use a CTE. But SQL Server 2005 is not yet available. >VIEWs are good, but they are part of a schema design and need to be You're wrong. As RDBMS professional and father of two darling children,>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] I can assure you that in both cases, neither size nor speed matters, but only what's released to the world at the end of the development cycle. If that's of lousy quality, you'll be sorry the rest of your life. If the end product is great, you'll enjoy each remaining day of your life! Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) "--CELKO--" <jcelko***@earthlink.net> wrote in message That I understand and as you say makes sense (well not exactly what you news:1127533898.848209.19600@g44g2000cwa.googlegroups.com... >>> Why not? << > > VIEWs can be built on VIEWs; they have to be created in order. Think > about it said, but close) > NO, NO, NO!!! You create Views because they are a solution (not the only >>> I am creating multiple Views that I am using temporarily << > > 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! > one) to a problem that works. I am not creating a good data model here, as you will and do, attest (or is it atest or "a test") :) >>> You are right. That is the way I think. So shoot me. :) << How about a stroke?> > 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! > I understand that.>>> 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. << > > 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] > But I am not trying to create a schema design here. I was trying to find a solution to a problem and there were various solutions, including cursors, which everyone agrees are the last resort. Once this is run, it will not be run again. Also, I have no control over the schema, here. I am just trying to move some data temporarily to my system from another. So setting up a correct schema seems a bit pointless. The answers you have already had are correct - you cannot create views
in a proc unless you use dynamic SQL. None of what you have said explains why you would want to do such a thing. Views are normally static. Procs, queries and functions are the correct places to put your parameters, not views. -- David Portas SQL Server MVP -- >> Why not? << VIEWs can be built on VIEWs; they have to be created in order. Thinkabout it >> I am creating multiple Views that I am using temporarily << NO, NO, NO!! You create VIEWs because they have meaning as dataelements 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! >> You are right. That is the way I think. So shoot me. :) << 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! >> 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. << VIEWs are good, but they are part of a schema design and need to beplanned 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] On Thu, 22 Sep 2005 14:40:22 -0700, tshad wrote:
(snip) >The problem is I also have a beginning date and ending date that I am using Hi Tom,>in my query after the Views are created. But I can't declare and set them >up until after the Create Views are done. If you really want the date constants at the beginning of the script, then you'll have to store them in a temp table, or create a view of constants. An example of the latter: CREATE VIEW StartAndEndDate AS SELECT CAST('20050101' AS datetime) AS StartDate, CAST('200512310 AS datetime) AS EndDate go >Also, is the multiple creation of Views a problem in a SP also? Yes. You can't create a view in a stored procedure. (Unless you'reprepared to let yourself in with dynamic SQL - and in that case, PLEASE read this first: http://www.sommarskog.se/dynamic_sql.html) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)
Show quote
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message But then how do I use these in select statement in another batch, such as:news:93hbj1d9pah0ug1rvkghkq6o4sc0fk7hsq@4ax.com... > On Thu, 22 Sep 2005 14:40:22 -0700, tshad wrote: > > (snip) >>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. > > Hi Tom, > > If you really want the date constants at the beginning of the script, > then you'll have to store them in a temp table, or create a view of > constants. An example of the latter: > > CREATE VIEW StartAndEndDate > AS > SELECT CAST('20050101' AS datetime) AS StartDate, > CAST('200512310 AS datetime) AS EndDate > go select x from y where y.someDate = StartDate and y.someOtherDate = EndDate > I rarely use DSQL, but will look at the article. It looks pretty good and >>Also, is the multiple creation of Views a problem in a SP also? > > Yes. You can't create a view in a stored procedure. (Unless you're > prepared to let yourself in with dynamic SQL - and in that case, PLEASE > read this first: http://www.sommarskog.se/dynamic_sql.html) is now bookmarked for later. Thanks, Tom On Mon, 26 Sep 2005 08:36:47 -0700, tshad wrote:
>"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message Hi Tom,>news:93hbj1d9pah0ug1rvkghkq6o4sc0fk7hsq@4ax.com... (snip) >> CREATE VIEW StartAndEndDate >> AS >> SELECT CAST('20050101' AS datetime) AS StartDate, >> CAST('200512310 AS datetime) AS EndDate >> go > >But then how do I use these in select statement in another batch, such as: > >select x from y where y.someDate = StartDate and y.someOtherDate = EndDate SELECT x FROM y CROSS JOIN StartAndEndDate AS saed WHERE y.someDate = saed.StartDate AND y.someOtherDate = saed.EndDate >>>Also, is the multiple creation of Views a problem in a SP also? Just remember that this article is primarly a warning against dynamic>> >> Yes. You can't create a view in a stored procedure. (Unless you're >> prepared to let yourself in with dynamic SQL - and in that case, PLEASE >> read this first: http://www.sommarskog.se/dynamic_sql.html) > >I rarely use DSQL, but will look at the article. It looks pretty good and >is now bookmarked for later. SQL. If you read it otherwise, go read it again. <g> Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics