Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ALTER STATEMENT INSIDE PROCEDURE Expand / Collapse
Author
Message
Posted Friday, May 07, 2010 10:54 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
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...

Post #918176
Posted Friday, May 07, 2010 1:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 22,511, Visits: 30,236
Why are you adding a column to a table inside a stored procedure?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #918334
Posted Monday, May 10, 2010 12:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
That's some particular situation that we have...
Post #918817
Posted Tuesday, May 11, 2010 12:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 5:24 PM
Points: 15, Visits: 295
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)
Post #919455
Posted Tuesday, May 11, 2010 12:59 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
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 ...............


Post #919467
Posted Tuesday, May 11, 2010 1:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 5:24 PM
Points: 15, Visits: 295
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
Post #919474
Posted Tuesday, May 11, 2010 1:25 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
thank you
Post #919481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse