CODEWITHSUNDEEP
t-sqlsql-server-2008stored-proceduresalter-table
TortTupper
TortTupper

Reputation: 61

Add a column and update it in same stored procedure in SQL Server 2008

if I have a stored procedure say

CREATE PROCURE w AS

ALTER TABLE t ADD x char(1)

UPDATE t set x =1

Even when it lets me create that stored procedure (if I create it when x exists), when it runs, there is an error on the UPDATE statement because column x doesn't exist.

What's the conventional way to deal with this, it must come up all the time? I can work around it by putting the UPDATE inside EXEC, is there another/better way?

Thanks

Upvotes: 5

Views: 9898

Answers (4)

Lavos
Lavos

Reputation: 31

The issue you are running into is that your update is being validated against the existing table before it will execute the create for your procedure.

If you're committed to this all you have to do is make sure that the table doesn't exist when you create/alter the procedure, which takes the parser down the deferred name resolution path due to the non-existent objects.

After the procedure is created, then you can create your table.

I'm assuming your process drops/creates the table already or you really wouldn't really need a procedure like this anyway.

Upvotes: 0

a.boussema
a.boussema

Reputation: 1116

I think that you should add a GO statement just after the Create.

Sql server will send the create so, and then your update will work fine.

Upvotes: -1

abatishchev
abatishchev

Reputation: 100258

ALTER TABLE in the context of 1st TRANSACTION and UPDATE in the context of 2nd:

CREATE PROCEDURE w
AS
   BEGIN TRAN
      ALTER TABLE ..
   COMMIT

   BEGIN TRAN
      UPDATE ..
   COMMIT
END

Upvotes: 4

Pranay Rana
Pranay Rana

Reputation: 176896

Rather than adding a column like this and then updating its value, you can add a column with a default value

CREATE PROCEDURE w AS 

ALTER TABLE t ADD x char(1) NOT NULL CONSTRAINT abc DEFAULT 1

Upvotes: 2

Related Questions