By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,983 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,983 IT Pros & Developers. It's quick & easy.

Alter Table AND Update Table with Variable MS SQL

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE PROCEDURE dbo.myProcedure_SP
  3.     (
  4.     @var1 int = 1,
  5.     @var2 int = 1
  6.     )
  7.  
  8. AS
  9.  
  10.  
  11. ALTER TABLE myTable
  12. ADD column1 INT NULL
  13. column2 INT NULL,
  14.  
  15. UPDATE myTable
  16. SET column1 = @var1,
  17. column2 = @var2
  18.  
  19. GO
  20.  
  21. EXEC myProcedure_SP
  22.  
  23.  
  24.  
Any ideas how to work around this? Thanks!
Dec 31 '07 #1
Share this Question
Share on Google+
1 Reply


Scrotehead
P: 5
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?
Jan 4 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.