Primary Key- char 12

Question

Primary Key- char 12

pzmrcd

Default port

Points: 1419
More actions
August 4, 2009 at 10:29 am

#137561

Hi
We have an existing application that uses a table with a Primary ket tat has been set to char[12].
This was developed many years back and due to the volume of data need to convert char12- char 15.
There are other aproc's using this field as input param . Is there a way to do this onversion with out effecting existing code?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

i would definitely do this using the SSMS GUI on a test database;

since it is a PK,the table will probably be dropped and recreated, and any indexes, foreign keys and other references need to be dropped and recreated, which is one of the things the GUI handles so well for us.

after that, if you do sp_refreshview 'YourTableName', all the views that reference that table will get refreshed with the new larger column definition;i forgot how to do procedures that reference the table in question, I've got to take a look in BOL; since you know some parameters for procs are CHAR(12), you'll need to fix those manually, but others just need to be recompiled i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

pzmrcd (8/4/2009)
Hi
We have an existing application that uses a table with a Primary ket tat has been set to char[12].
This was developed many years back and due to the volume of data need to convert char12- char 15.
There are other aproc's using this field as input param . Is there a way to do this onversion with out effecting existing code?
Thanks

No. You will need to change the input parameters for those stored procedures from 12 to 15 as well. Just changing the data in the table will not be enough.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

No. You will need to change the input parameters for those stored procedures from 12 to 15 as well. Just changing the data in the table will not be enough.

And don't forget to change any other variables/table variables/temp tables etc inside the stored procedures that may be used to hold temporary copies of the primary key.

It's going to be a long slog, but you will probably need to look at each of these stored procedures in detail (or at the very least search for all instances of "12" in those procedures).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Heh... that's why I plan on "growth" and I'll always make VARCHAR variables about 50% bigger than they need to be.

I do wish that MS would come out with type inheritance in the definition of parameters and variables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

Jeff Moden (8/5/2009)I do wish that MS would come out with type inheritance in the definition of parameters and variables.

Isn't that what UDTs go some way to providing, as long as they are alias based?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Ian Scarlett (8/5/2009)
Jeff Moden (8/5/2009)I do wish that MS would come out with type inheritance in the definition of parameters and variables.
Isn't that what UDTs go some way to providing, as long as they are alias based?

I suppose, but it just doesn't seem like you should have to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions