Stored Procedure - SQL Server 7

[JazzBass]

Hi all,

Over the weekend I tried to write a stored procedure that included Select... Into, Alter Table, and Update statements.

I tried adding GO after each statement and that worked fine in the query analyzer. I tried copy and pasting the statements into the stored procedure and it only saves the first statement before the first GO.

Next I tried deleting the GO statements, but then the alter statements do not work.

Do I need to put the Alter Table and Update statements in separate stored procedures or is there any way around it?

Thanks,
JazzBass

[techgnome]

I've done this a number of itmes, if you could post the sp that you tried to create I might be able to help.

[JazzBass]

I don't have it here with me, but here is a little something.

Select Max(fldDate), fldPerson, fldState into Table2 from Table1 where fldState = 'RI'
GO

ALTER TABLE Table2 Add (fldPerson2, varchar(15))
GO

Update Table2 set fldPerson2 = 'NAME1'
GO

Something like that. Real basic stuff.

Thanks,
JazzBass

[techgnome]

Here are somethings to keep in mind:
1) SPs are generaly used for a repeatable process w/ different parameters.
2) That said, ALTER TABLES are not usually found in SPs. Is there a reason for including it?
3) If you do need to include it, as written, it will attempt to add the coll EVERY time the SP runs. You should include a check to see if it is there first.
4) Also, if you need to include the ALTER TABLE, wrap it in a transaction (BEGIN TRANSACTION / COMMIT TRANSACTION)... The UPDATE will fail unless the ALTER has been first completed. This would require that there be a GO after the ALTER. But... Since CREATE PROCEDURE uses the first GO to mark the end of the SP, the SELECT gets included, but the rest does not. To get around this, wrap the ALTER in a BEGIN/COMMIT TRANSACTION, then the UPDATE should work.

[JazzBass]

Thanks for the help.

What I forgot to mention is that I'm also dropping the table and recreating it. I've got a somewhat complex select statement searching for a max date for each state basically and is inserting that into the new table. I forget exactly what the problem was, but I wanted to only choose the max state, then find the person that belongs to that record and it would not work right, unless I added the person later. That's why I need to delete the table, unless I can just empty the table and insert the records over again. I may try that and see how I like it.

Thanks again for the tips.
JazzBass