Altering a table with in a stored procedure

Question

Post reply

Altering a table with in a stored procedure

Vishal-234972

Mr or Mrs. 500

Points: 515
More actions
October 2, 2007 at 11:49 pm

#153873

Hello Everyone,
I am facing a problem when I create a temp table and then insert records into that and later with in that stored procedure I add a new column and try to insert and update records in the same table and then it causes problem which states the newly added column doesn't exists while just after adding the new column if I use sp_help system stored procedure to get details if the newly added column exists of not then it shows the new column.
Here is the T-SQL of that stored procedure and help me out:
CREATE PROC test_proc1
AS
BEGIN
CREATE TABLE test_table1 (col1 int not null, col2 varchar(50) null)
INSER INTO test_table1 VALUES (101, 'Temp Data1')
INSER INTO test_table1 VALUES (102, 'Temp Data2')
ALTER TABLE test_table1 ADD col3 varchar(50) null
exec sp_help 'test_table1'
INSER INTO test_table1 VALUES (103, 'Temp Data3', 'New Col Data1')
DROP TABLE test_table1
END
Thanks
Vishal

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

The SQL Server does not know which columns to insert!!!

CREATE PROC test_proc1

AS

BEGIN

CREATE TABLE test_table1 (col1 int not null, col2 varchar(50) null)

INSERT INTO test_table1 VALUES (101, 'Temp Data1')

INSERT INTO test_table1 VALUES (102, 'Temp Data2')

ALTER TABLE test_table1 ADD col3 varchar(50) null

exec sp_help 'test_table1'

INSERT INTO test_table1 (col1, col3) VALUES (103, 'Temp Data3')

DROP TABLE test_table1

END

GO

N 56°04'39.16"
E 12°55'05.25"

Vishal-234972 Mr or Mrs. 500 Points: 515 More actions · Answer 2

Sorry Peter, my mistake. I edited the last insert statement. Please try to execute the same and then you get to know what's the prob.

Anyway...thanks 🙂

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

It's called SCOPE.

After ALTERING a table, an insert can't take place directly.

However, there is a workaround

CREATE PROCtest_proc1

AS

BEGIN

CREATE TABLE test_table1 (col1 int not null, col2 varchar(50) null)

INSERT INTO test_table1 (col1, col2) VALUES (101, 'Temp Data1')

INSERT INTO test_table1 (col1, col2) VALUES (102, 'Temp Data2')

ALTER TABLE test_table1 ADD col3 varchar(50) null

exec('INSERT INTO test_table1 VALUES (103, ''Temp Data3'', ''New Col Data1'')')

select * from test_table1

DROP TABLE test_table1

END

GO

exec test_proc1

drop PROC test_proc1

N 56°04'39.16"
E 12°55'05.25"

Vishal-234972 Mr or Mrs. 500 Points: 515 More actions · Answer 4

Thanks Peter,

I was doing the same thing but I was thinking if I did something or I may be lacking somewhere.

Thanks.

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Vishal,

Just a thought... Is this a permanent nonreporting you're modifiying? The reason I ask is if you're actually doing this to some "real" data (other than a reporting table), you may actually be violating all sorts of good practices including things like 3rd normal form... and those violations can make your life very difficult in the future. For example... adding month columns to something like a CostDetail table will make it very difficult to work on all costs from all months and years...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)