stored procedures

[ronaldrahme]

Hi all
i have a table called dbo.priorites which contains 2 columns
_codepriorite varchar (20) primary key
_priorite varchar (50) null

so i was asked to change _codepriorite varchar (20) into _codepriorite int(4)

it worked with this script

alter table dbo.priorites
drop constraint pk_priorites
go

alter table dbo.priorites
drop column _codepriorite
go

alter table dbo.priorites
add _codepriorite int identity
go

alter table dbo.priorites
add primary key (_codepriorite)
go


i wanna use this script from the compiler , like with stored procedures... I wanna call this script from the software im working with... Any ideas on how to do it? ill be thankful for some help...

[krishnaindia2007]

Do you want to write general script or script specific to this table only?

If it is a general script pass table name and column name as parameter.

You can't execute DDL statements directly in procedures. Use EXECUTE IMMEDIATE to execute above statemetns in procedure.

[ronaldrahme]

Thnks krishna for your help
i made a stored proceduren i checked the syntax, it was okay:

create procedure sp_priorites

as
execute immediate
alter table dbo.priorites
drop constraint pk_priorites
go

execute immediate
alter table dbo.priorites
drop column _codepriorite
go

execute immediate alter table dbo.priorites
add _codepriorite int identity
go

execute immediate
alter table dbo.priorites
add primary key (_codepriorite)
go

do you think it might work this way?

[ronaldrahme]

oops its not workin, just another kst> how to use execute immediate? its not working for the above statements

[krishnaindia2007]

Here is the example

 
create or replace procedure test_proc as 
string varchar2(100); 

begin 
string := ' alter table test_data add constraint test_data_pk primary key (x)'; 
execute immediate string; 
end; 
/