How to change a stored proc's parameter datatype - without editing a CREATE PROC script

Question

How to change a stored proc's parameter datatype - without editing a CREATE PROC script

Treehouse

Ten Centuries

Points: 1234
More actions
May 3, 2010 at 1:35 pm

#222092

Let's suppose I inherit a database with 1000 stored procedures, most of which have a SMALLINT parameter named @CustomerID.
Now lets suppose our CustomerID column has exceeded the capacity of a SMALLINT and we need to convert it to an INT.
Modifying the columns in the related tables is easy enough- I can fire off a query against sys.coulmns that generates a series of ALTER TABLE statements which alter the CustomerID column to an INT.
But my stumbling block seems to be how I can modify the @CustomerID parameter for many stored procedures. I 'd really like to avoid having to script out all the procs and manually edit each one. I hope I am missing an obvious easy/elegant solution!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You will need to script ALTER PROCEDURE statements, edit each one and run the whole lot. If it's a parameter with a constant name, it should be possible to get one of the more powerful text editors (not notepad), to do a find and replace to do the editing for you.

You can generate all the stored procedure statements from management studio (Tasks-> Generate scripts), then do a search and replace on the parameters.

There's no 'Update sys.parameters' method of doing this.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 2

I'd be very surprised if there isn't a tool from RedGate (an SQLServerCentral.com sponsor - click their ad at the top right of the page) that can do this for one, but ja, scripting them out and doing a VERY CAREFUL search and replace should do the trick. Just please note that you will have to change "CREATE PROCEDURE" to "ALTER PROCEDURE" for the scripted out stored procedures.

If you don't know which text editor to use, you can use SSMS's query text area, or you can try out Notepad++[/url].

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Yeah, Red Gate's SQL Refactor can do that type of work for you. You would still want to test, test, test, to be sure it got things right.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Treehouse Ten Centuries Points: 1234 More actions · Answer 4

Thank you for the feedback. I ended up writing an SMO app in c# that scripts out affected procs, modifies the param datatypes according to specs I have stored in a table, then runs a drop/create for each proc.

A bit... inelegant... I admit. But since some of these procs date back almost 10 years and have inconsistent parameter naming, this is probably the safest & most thorough approach other than editing each proc manually.

Thanks again,

Matthew

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Actually no, that sounds like a pretty good solution. Test, test, test though.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 6

And just in case Grant didn't mention it, test again.

But seriously, for this kind of change you *REALLY* want to be sure everything works. And in your testing be sure to use a value that is less than the maximum SMALLINT size and a value that is greater than that size.

Treehouse Ten Centuries Points: 1234 More actions · Answer 7

Testing? We have end users available to do that. 😀

Actually, we do have a test team that will be hammering on the system - full regression testing for this release, whew.

I can easily verify the changes by just comparing the output from sys.parameters between the newly modified database and a linked version of the original database. I have my master list in excel of all procs and parameters that need to be changed (this is what is used to populate the table that the SMO app uses to decide which procs/params to change) and the comparison list that I generate between the new and original databases should match my "master list". If it doesn't, then I know there's some clean up to do.

I'd post my C# code, but I fear I am more of a DB person than a C# developer, and it includes too much "special case" code that really only applies to this particular project. In the future I may clean it up to make it more generic... FWIW, I will share my algorithm:

This requires each parameter to be defined on a separate line in the procedures.

1) Pull a list of of proc names, parameter names, original data types, and new datatypes

2) For each proc in #1, script out the CREATE code

3) Split the script into separate lines (based on CRLF)

4) Loop through each param datatype change for this proc

5) Examine each line of the proc to see if it's a "parameter" line ... e.g. for our purposes, this regex: "(?<=(^\s*,\s*|^\s*))@ParamName\s+"

.....a) If the param is what we're looking for, format the line by removing unnecessary spaces

.....b) Search for the old datatype and replace it with the new data type

..........i) if the old data type was not found, log the proc/param/datatype info for further investigation

.....c) stop processing this param (to avoid subsequent references of the parameter later in the proc body) and continue to next param in #4

6) Script a DROP to a text file for this proc

7) Write the lines (incl modified ones) from steps 3-5 to the same text file

And at this point you could execute the procs individually via the app- I prefer to do it by batch executing the output files to confirm they are working before submitting them back into source control.

So maybe someone else embarking on a similar project might find this helpful. At the risk of moving OT, out of necessity for this project I learned a great deal about regular expressions and found Derek Slager's online regex tool to be indispensable: http://derekslager.com/blog/posts/2007/09/a-better-dotnet-regular-expression-tester.ashx

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

It sounds like you've got something working with SMO, but as a side note for anyone interested, the following will list all parameters of datatype 'smallint' for all user created stored procedures.

select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name, t.name param_type

from sys.all_parameters p

join systypes t on t.xusertype = p.user_type_id

join sys.objects o on o.object_id = p.object_id

where o.type = 'P' -- type procedure

and t.name = 'smallint' -- data type

and o.is_ms_shipped = 0-- user created

order by

schema_name(o.schema_id),

o.name;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho