Using GO in a stored procedure
-
First off, apologies if this has already been asked, I can only get the search facility to return the first ten records, the next/last buttons don't seem to work (?).
My problem is I appear to need the GO command in the middle of a stored procedure. When I create the procedure it will only proceed to the first GO command (as far as I can see) and then it stops. I've tried taking the GO command out and that doesn't work - I suppose I could create the table permanently and then delete all the records after I've used it, but frankly this is annoying me and I'm sure there is a simple answer....
btw - Using 2000 MSDE sp3 from SQL2000 Query Analyser
I wrote this stuff about a year ago and frankly I don't know why it doesn't work, for example in the create table command I use "ON [Primary]" and I have no idea why I did that or what it means (explanation welcomed).
Anyway, here's the command :
ALTER PROCEDURE InkAnalysis
AS
DROP TABLE JOBPROBLEMS
SELECT * INTO JOBPROBLEMS FROM JOBCOSTING WHERE TotalCost <= 0 and WGSM <= 0
CREATE TABLE #CLEANEDSTATS (
[Mon] [int] NULL ,
[Yea] [int] NULL ,
[JOB NUMBER] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[TP NUMBER] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[RUN LENGTH] [float] NOT NULL ,
[REEL WIDTH MM] [float] NOT NULL ,
[INK CODE] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[Coverage] [float] NOT NULL ,
[MaterialIssue] [float] NULL ,
[PR Issues] [float] NULL ,
[PR Returns] [float] NULL ,
[Total Weight] [decimal](38, 4) NULL ,
[WGSM] [decimal](38, 8) NULL ,
[WGSM1] [decimal](38, 8) NULL ,
[TotalCost] [decimal](38, 2) NULL ,
[SQM] [decimal](38, 2) NULL ,
[£per100SQM] [decimal](38, 2) NULL
) ON [PRIMARY]
GO
****When I create the procedure it stops here, it won't save anything after the GO command into the SP, however, the code when run directly from QA is fine.......
Any ideas appreciated.
Thanks
Rich
-
GO is a batch separator in SQL so it starts a second batch after the GO statement so as far as SQL Server is concerned the Create or Alter Procedure process ends at the GO. If you are using SQL Server 2000 or later you could and probable should replace your temporary table #CLEANEDSTATS with a table variable. If you do not want to do that, you can just leave out the On Primary because you do not need that when creating a temporary table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
The word GO is the transaction or process terminator. You do not need to terminate each step in a procedure but you must terminate the procedure. Move your GO to the very end and everything will work just fine. Look at the total procedure as a unit with GO at the end.
Another thought. Why are you building static tables in the proc? If the data is of no use and must be refreshed then you might want to build the table out side of the procedure and just leave it. Then your procdure would remove the data from the table using either the "Truncate Table" or "Delete from" statements. Your procedure will run better.
Good Luck
-
Gotcha, thanks Jack.
I moved the GO to the end of the query and it worked fine after I deduped a few variables and table names.
I actually have an exact replica of the table build a little further down, which was confusing me, because when I changed the first table build it was throwing an error on the second with the same error message....
The 600 odd lines of spaghetti complicated my life somewhat.
-
Good advice JMC, but I'm running it off the back of a third party app and this is a once a month query, I don't like creating tables in a third party db. I suppose I could get around it by creating another DB, but it adds a second part to the working of the procedure making a rebuild more complex....
Although I suppose I'm rebuilding off a script anyway, so another part won't hurt - if I get performance problems I'll think about, otherwise - it works...
-
Have you tried ; (semi colon)
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: -
Yeah... but I hate Oracle
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There is one way to get around the GO statement.
In the middle of an sp, I needed to do an ALTER TABLE but the code that followed it didn't recognize the change because the batch was still executing. Then I tried using GO after the ALTER TABLE statement but then I lost any variables declared earlier in the batch.
I found that I could place the ALTER TABLE statement into a variable e.g.
SET @sql = 'ALTER TABLE bla bla bla'
And then use
EXEC ( @sql )
and the following code did recognize the table change, without needing GO and without losing the variable declarations.
Not sure if it helps in your specific case, just another idea. But it does work in both SS 2000 and 2005.
-
Thanks William - top tip, I'll remember that one....
-
Some additional info
http://blog.sqlauthority.com/2007/05/11/sql-server-explanation-sql-command-go/
Kindest Regards,Pinal Dave
sqlauthority.com -
But this works.
USE
AdventureWorks;
;
DECLARE
@MyMsg VARCHAR(50)
SELECT
@MyMsg = 'Hello, World.'
;
-- @MyMsg is valid after this.
-- Does not yield an error because @MyMsg is declared in this batch.
PRINT
@MyMsg
;
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply