Execute multiple Batches in a stored procedure? 1

[jgoodman00]

We have a number of fields which are currently graded as 'G', 'A', or 'P'. I am in the process of converting these to integer values based on a lookup table to this. In order to re-classify our existing data I wrote the following stored prcedure:

CREATE PROCEDURE dbo.Conditions AS
UPDATE InspectionImporter
SET AirpipeC = tblLUPCondition.ConditionID
FROM InspectionImporter INNER JOIN tblLUPCondition
ON InspectionImporter.AirpipeC = tblLUPCondition.Condition
GO


We have approximately 25 similar fields, so rather than creating & saving 25 seperate procedures, I thought I could create one, which contains all of the required update statements, & set it to run in batches as follows:

CREATE PROCEDURE dbo.Conditions AS
UPDATE InspectionImporter
SET AirpipeC = tblLUPCondition.ConditionID
FROM InspectionImporter INNER JOIN tblLUPCondition
ON InspectionImporter.AirpipeC = tblLUPCondition.Condition
GO

UPDATE InspectionImporter
SET AntiC = tblLUPCondition.ConditionID
FROM InspectionImporter INNER JOIN tblLUPCondition
ON InspectionImporter.AntiC = tblLUPCondition.Condition
GO


If I write this in the SQL query analyzer it executes perfectly. However, the stored procedure only saves the first statement, up to the first GO statement. Anything written after this appears to be deleted.
How else can I go about doing this, without saving each batch as a procedure? James Goodman
j.goodman00@btinternet.com

 

[cdukes]

Remove the GO after the first update query and the stored procedure will be created with both statements.

Hope this helps,

Chris Dukes

 

[jgoodman00]

I tried this, & then it doesn't appear to execute all of the statements. The completed procedure contains 12 batches, & omitting the go command between each individual statement leads to only the first few being run (I think it ran 4). The remainder stay unchanged, whereas executing them seperated by GO commands seems to work... James Goodman
j.goodman00@btinternet.com

 

[tlbroadbent]

James,

GO indicates end of batch. If you execute your script in Query Analyzer, the first batch creates a stored procedure. The 2nd batch and all that follow update the table. Once the table is updated with a batch, it will not be updated again because it no longer meets the condition. The value changed the first time you ran the script.

When you remove the GOs, the script will create the stored procedure again, if you have dropped it. If you have not dropped it, you will get an error stating it already exists.

Once you've created a stored procedure, you will need to execute it in order for the updates in the SP to be executed. I don't see the need for a stored procedure if this is a one shot process. Just run the updates.

I do question whether any updates will actually occur with your code. You state that you are converting alphabetic values to integers. Obviously, the data type in the InspectionImporter table cannot be integer if it contains alpha characters. Is the data type of ConditionID in the tblLUPCondition also character or is it integer? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cdukes]

This should work fine I have written stored procedures with many more update statements than this, I am presuming that the Stored procedure has been created with all 12 statements in it?

Try putting some debug statements in the SP to ensure that it is going through the code.

eg. Print "Updated batch 1"

OR.
SELECT * FROM InspectionImporter INNER JOIN tblLUPCondition
ON InspectionImporter.AntiC = tblLUPCondition.Condition

etc.

If you post the actual SP then we can have a better look at what you are trying to do.

Chris Dukes

 

[jgoodman00]

Terry,

You are correct in assuming that the fields are char in InspectionImporter. However, all of the ID fields whose values are replacing the text values are integers. The purpose of this table is simply to convert our existing datatypes into (hopefully) more efficient datatypes (almost every single field is now either a lookup integer or a bit field. This table will then have its results appended into the actual table to be used in the new database.
You are also correct in thinking this is a one-shot process. However, at the moment I am importing our existing data so that I can use it to write the rest of the app to go with it. Once this is written & tested, the most current data will be imported. I therefore don't want to have to write all of these procedures again.


I have ended up by saving this procedure as an SQL statement (through the SQL analyzer) instead of using it as a stored procedure, for the reason you stated.



For reference purposes, am I correct in thinking that if I remove the 'GO's & save it as a stored procedure, it SHOULD carry out every individual update, & not just stop after a few of them?
James Goodman
j.goodman00@btinternet.com

 

[tlbroadbent]

You are correct. If you remove the Gos and create a stored procedure. Upon execution, all of the updates should occur.

I have one additional observation. I wouldn't create a SP and run all the updates together. I would simply create the script as you have done. Retain the GOs between each update statement. Add some Print or Select commands to display comments between updates. It makes monitoring the process much easier.

Example:

Select "Update AirpipeC started" = getdate()
GO
UPDATE InspectionImporter
SET AirpipeC = tblLUPCondition.ConditionID
FROM InspectionImporter INNER JOIN tblLUPCondition
ON InspectionImporter.AirpipeC = tblLUPCondition.Condition
GO

Print ''
Select "Update AntiC started" = getdate()
GO
UPDATE InspectionImporter
SET AntiC = tblLUPCondition.ConditionID
FROM InspectionImporter INNER JOIN tblLUPCondition
ON InspectionImporter.AntiC = tblLUPCondition.Condition
GO Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jgoodman00]

Thats basically what I have done. I saved it as an SQL file, & by executing it through the analyzer it then prints to the screen how many records have been updated & any errors which have occured... James Goodman
j.goodman00@btinternet.com

 

[aalmeida]

Regarding your commnet " I therefore don't want to have to write all of these procedures again." I would strongly recoment for you to some Source Safe like MS Visual Source Safe. These kind of SW help you not only to keep your code so you don't have write it again as well as keep track of them into a central Code repository where you have several util information about your code. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"