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.
| Author |
Topic |
|
yenny.lauw
Starting Member
14 Posts |
 Posted - 2006-08-02 : 22:28:13
|
| hi all,Is there anyone who can tell me how to update parameter data type in stored procedure via transact sql ?Eg :Create procedure test( @Parm varchar(10))Select @ParmI need to change varchar(10) into varchar(20), the problem is there are a lot of stored procedure in the database, is there anyway to automate the changes ? maybe access it from sysobjects ?Thanks |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-08-02 : 23:09:16
|
| Hope this helps.............Select * from information_schema.routines where routine_definition like'%@Parm varchar(10)' |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-02 : 23:11:37
|
i am afraid there is no easy way to do this.If you have the SP script in text file, just update the text file using text editor (find and replace) and then update to your database again.If you do not have the SP in text file, just script it out via Enterpise Manager.
KH |
|
|
|
yenny.lauw
Starting Member
14 Posts |
Posted - 2006-08-03 : 00:24:58
|
hi khtan,is that mean I still have to change each SP manually ?  (thousands of SP)and sqllearner, is it okay if I just change the routine definition from information_schema.routines ? or is there any risk/effect I should know ?eg:update table information_schema.routinesset routine_definition = ... Thanks In Advance |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-08-03 : 00:30:05
|
| The best way is as khtan mentioned to script out and replace all.But the above mentioned also should work..try for one or 2 and then try the bulk update |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-08-03 : 00:31:42
|
| It's not necessarily a manual operation. Here's how I would go about it:-Write a small VB app that uses the SQLDMO API to script out the stored proc-Do a string search/replace-Delete the existing stored proc-Insert a new one using your modified stringYou'll need to be careful because it could affect other things in your procedures - e.g. data type incompatibilities etc.If you're doing this because of a change in an underlying table, I would investigate using some RAD software to automatically create the basic INSERT/UPDATE/DELETE stored procs for you. It saves you a lot of coding.HTH,Tim |
|
|
|
yenny.lauw
Starting Member
14 Posts |
Posted - 2006-08-03 : 01:12:06
|
ok, thx for all response, I'll try to do it as timmy suggestions  |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-03 : 01:41:42
|
quote:
Originally posted by yenny.lauw
ok, thx for all response, I'll try to do it as timmy suggestions
But backup your database & script out your first.
KH |
|
|
|
|
|
|
|
|
Update Parameter data type in Stored Procedure