Procedure Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Procedure Problem

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-30 : 07:01:21

hi to all experts Now again i dont know what is going wrong,,,,

Actually in my procedure i want to add a new column to my table first then i used to run an update statement only,,,
so i make a procedure something like that..

Create procedure MyProc
as
BEGIN
Alter table My table add remark varchar(50)

update Karclos1009 set remark=(case when condition1 then '1'
when condition2 then '2'
when condition3 then '3'
when condition4 then '4'
when condition5 then '5'
when condition6 then '6'
end)

end
go

But it gives me error......

Invalid column name 'remark'.
so any idea about this...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 07:10:18

Dont have procedure to alter the table. Alter it and then use procedure to update that column

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-30 : 08:11:19

Yes its working fine when i do like this,,, its working fine but i m just curious to know that if it is possible...
Thanks..

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 08:31:57

you'll need to run that update dynamic I think. try this,


Create procedure MyProc
as
BEGIN
Alter table My table add remark varchar(50)

exec ('
update Karclos1009 set remark=(case when condition1 then ''1''
when condition2 then ''2'' 
when condition3 then ''3''
when condition4 then ''4''
when condition5 then ''5''
when condition6 then ''6''
end)
')
end 
go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 08:51:00

quote:

Originally posted by sakets_2000

you'll need to run that update dynamic I think. try this,


Create procedure MyProc
as
BEGIN
Alter table My table add remark varchar(50)

exec ('
update Karclos1009 set remark=(case when condition1 then ''1''
when condition2 then ''2'' 
when condition3 then ''3''
when condition4 then ''4''
when condition5 then ''5''
when condition6 then ''6''
end)
')
end 
go

You cant run the procedure again as it would throw error for already available column

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-30 : 08:52:57

I know that. Since the OP asked for a possible way of compiling the procedure, I gave him one.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-01 : 05:26:24

Ok Thanks to all....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources