question

Bhupendra99 avatar image
Bhupendra99 asked

How to handle the Inserts which are not having column list in case of a New column added

Suppose There is a Table Called List its has 2 columns Id and Name There are many sp which are having code as Insert Into List Values(@Id,@Name) If I add a new column say Sequence all the Old sp will start giving error column name or number of supplied values does not match table definition Is there any way we can handle this without changing all the Sp I tried Default but it didn't worked out
insert
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
You'll need to edit those procedures. There's no other way around it. I wouldn't recommend writing the code that way in the first place. Enumerate the columns that you're inserting into as well as the values. At least in that case, if the new column was nullable or had a default value, the procedures would continue to work.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
This is why you shouldn't do inserts without a column list. I'd say you have to rewrite your stored procedures. A workaround if the number of procedures is massive and you can't get around to doing it just now would perhaps be to rename the table and create a view which does a SELECT id, name FROM "new table name" But I wouldn't really recommend doing that, you most probably add a column to the table because it is meaningful in some way. And your other queries which needs the new column from the table would have to be changed to use the new name of the table, and that way you end up with code which uses two different names for essentially the same object. If the looks of your stored procedures are pretty consistent, you could create a C# program which uses SMO-objects to get the text from all stored procedures and have a RegEx object change your inserts to explicitly use a column list instead of just insert into "tablename" values(...). That requires some knowledge about C# and regular expressions, but you'll probably be able to google for it. If you don't want to have SMO actually alter the procedures, you can set SqlExecutionModes property of the ConnectionContext object to SqlExecutionModes.CaptureSql. That way, your sp.Alter() method calls won't actually alter the procecure, instead you can find the DDL in a string Collection in ConnectionContext.CapturedSql.Text. Just loop through it and write the string values to a text file which you can Review Before executing it in Management Studio.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.