Creating and inserting values in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating and inserting values in a column

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-08-16 : 08:29:33

In my SP, I wantt to create a new column and insert some values, so I have:

CREATE PROCEDURE InsertNewColumn
As

alter table test
add pon bit

insert into test (pon) values (0)

GO

But, I cannot create this SP because I get error that column PON does not exist.

How can I add a new column and add some values to it, in a SP??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 08:47:33

Try this

CREATE PROCEDURE InsertNewColumn
As

alter table test
add pon bit

GO

insert into test (pon) values (0)

GO

Madhivanan

Failing to plan is Planning to fail

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-16 : 08:50:05

That's right because when the sp is "compiled", pon does not exist.

Creating and altering database objects on the fly is a big no-no and is usually indicative of a design flaw of an application that will be short lived and will crash and burn under the weight of it's own ugliness.

I would reconsider this approach. This might not be the answer you wanted, but it is the one you needed.

====================================================
Regards,
Sean Roussy

Thank you, drive through

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-08-16 : 08:54:12

Madhivanan- I am still get the same error: Invalid column name 'pon'

Thrasymachus, this IS the answer I wanted , because I want to learn the best approach to modify the production server. I am a beginner in the world of DBs.

So please inform me more on the best appraoch.
kind regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 08:58:03

As said, as a beginner you have to use this for testing purposes and dont need to implement it in the prodcution server. If you want to modify the table run it as a query and dont put it in sp and call it

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 08:59:10

>>Madhivanan- I am still get the same error: Invalid column name 'pon'

Did you execute the code?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-16 : 09:08:52

if you really want to do it in sproc (DON'T)
you must do
exec('alter table test add pon bit')

Go with the flow & have fun! Else fight the flow

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-16 : 09:09:15

What you want to read about is Normalization.

The only way to not be a beginner is "to do" and "to read". There is a lot of great stuff in SQL Server Books Online. Shamefully I am just getting around to reading Kalen Delany's Inside SQL Server 2000 2nd Edition but it is great and I thought it was going to be painful. The Ken Henderson books are really good too.

Be weary of message boards. Too often people will give you an answer to your question that will work but is not necessarily the best way to go about things. Just like everything on the internet, take it with a grain of salt.

No offense intended to anyone here.

====================================================
Regards,
Sean Roussy

Thank you, drive through

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 09:13:21

quote:
Be weary of message boards. Too often people will give you an answer to your question that will work but is not necessarily the best way to go about things. Just like everything on the internet, take it with a grain of salt.

Thats correct
All the update and Delete statements suggeted in the Forums should be run in the test server and if one is satisfied with the result, then should run in the Production server

Madhivanan

Failing to plan is Planning to fail

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-16 : 09:25:29

nobody has discounted my original point:

"Creating and altering database objects on the fly is a big no-no and is usually indicative of a design flaw of an application that will be short lived and will crash and burn under the weight of it's own ugliness."

====================================================
Regards,
Sean Roussy

Thank you, drive through

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources