Alter Table AND Update Table with Variable MS SQL

[islandtalker]

Hi,

In a stored procedure (MS SQL) I want to update a table to Add columns to it, and then update those new columns with data from a variable.

The problem is that, after updating the table I add a GO, the variable value is loss, but if I don't add the GO I get the error that the column Im adding value to don't exist.

o_O

Example:

 
CREATE PROCEDURE dbo.myProcedure_SP
    (
    @var1 int = 1,
    @var2 int = 1
    )
 
AS
 
 
ALTER TABLE myTable
ADD column1 INT NULL
column2 INT NULL,
 
UPDATE myTable
SET column1 = @var1,
column2 = @var2
 
GO
 
EXEC myProcedure_SP
 
 
 

Any ideas how to work around this? Thanks!

[Scrotehead]

I'm not sure what your overall aim is here. Why are you creating a sp and then attempting to run it straight away? If this is a creation script why have an sp, just run the alter and update statements directly. If you need to reference variables throughout a long creation script that include proc creations try creating a temp table at the start to hold all your reused parameters then assign from that table as required.
Are you attempting some dynamic table control from another app?