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?