May 7, 2010 at 10:54 am
Hi,
If i alter a table and then try to insert a record into it i get an error....
intruction:
ALTER TABLE table1 ADD id6 int
insert into table1 values (1,1,1,1,1,1)
But if i do like this, i get no error:
ALTER TABLE table1 ADD id6 int
exec('insert into table1 values (1,1,1,1,1,1)')
Isn't there other way of doing this without using EXec commands?
Note - i can not use GO after the alter because i'm inside a stored procedure...
May 7, 2010 at 1:42 pm
Why are you adding a column to a table inside a stored procedure?
May 10, 2010 at 12:51 am
That's some particular situation that we have...
May 11, 2010 at 12:07 am
You cannot add a column and insert a value on the same stored procedure...
When sql server starts to execute the code it validates the script... In your case he sees tha t the alter statement is ok (without executing it) but when it gets to the insert statement it throws an error (invalid column)
The solution is to move the alter table to another stored procedure.....
PS: using exec, the code is only validated at runtime , that why you dont get aqn error
To verify the issue try this
between the alter and insert statement write a block that does nothing for 1 minute....
You 'll see that the error will rise when you hit Run (on validation) and not after 1 minute (exec)
May 11, 2010 at 12:59 am
Ok, still i have a question.
If i do like:
alter table .......................
exec (insert into ...............)
It works! and now i understand why, it's because the exec dont let the compiler see the insert statement.
But why does the opposite don't work?
exec(alter table .....................)
insert into ...............
May 11, 2010 at 1:14 am
When sql server starts to execute the code it validates the script...
Validated NOT executes
so the steps are
1. validate exec statement -> ok, continue (DO NOT EXECUTE)
2. Validate insert statement -> VALIDATION ERROR no such field exists
May 11, 2010 at 1:25 am
thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply