Can't change the DDL for the table (unless I upgrade the database by rolling out with the latest update version) because we go through a full QA cycle before any changes to Tables / Columns are allowed (existing code may depend on the tables being the way they were), so where we want to rollout (piecemeal) an SProc, which needs some DDL changes, we work around that by having a forward-compatibility VIEW.
Its a PITA though ... only comes about with Clients that don't have a maintenance contract (which would have gotten their system updated to Latest Version, so would have avoided the problem in the first place) and then that Client wants "a really simple report", or somesuch, which we reckon we can easily do (e.g. we have something that "almost does that already" and thus only requires a small change) ... and THEN we discover that that code is dependent on some new improvement we made and needs a DDL change ... and so on.
At that point we should just shoot the client ...
It normally goes something like this:
"I want to add these TWO new Columns" ... and (maybe) "Actually that table uses the old column-naming convention, if we are adding two columns let's fix up the column names too".
Create a Script to do the Column renames, ADD the new columns and rename the Table from MyTable_V1 to MyTable_V2. Add that script to the "Next Version Rollout Package"
Create a backward-compatibility VIEW for MyTable_V1 - basically just:
CREATE VIEW MyTable_V1
SELECT NewCol1 AS OldCol1,
So all the existing code will work (because it will, now, reference the MyTable_V1 View instead of the MyTable_V2 Table)
Then we change any Sprocs /Triggers / etc. to use MyTable_V2. MAYBE all code referencing MyTable_V1 gets Found and Fixed (but MAYBE we decide to only fix part of it). When there are no longer any references to MyTable_V1 we add a DROP VIEW MyTable_V1 to the rollout script for the next release.
At some point we do a Full QA cycle, and build a Release Package. We can then roll out the Release Package to all clients that are entitled to it.
THEN ... we have to do some work for a client that hasn't had the rollout. So THEN we create a Forward-Compatibility VIEW
CREATE VIEW MyTable_V2
SELECT OldCol1 AS NewCol1,
CONVERT(int, 1234) AS SomeNewColumn
Normally I would not expect anything (which needed the new table) to want to INSERT to it - that's going to almost certainly need the "real" new Columns ... so at that point the Client would have to upgrade to the new version.
But in this case there is indeed an INSERT in the Sproc, but it is definitely not going to be called, so I want to leave the INSERT in (rather than create a bespoke, one-off, version of that SProc for that client) ... but SQL doesn't like it (as per my O/P)
Simple answer, as @ScottPletcher said, is to put an INSTEAD OF TRIGGER on my forward-compatibility MyTable_V2 view ... then in the event that the SProc DID execute the INSERT statement all would be well ...
... but because this is just String & Gum to Elastoplast this Client until they stump-up for the upgrade I was trying to do as little work as possible.
Now you lot can stop rolling around on the floor laughing, at my expense, and get on with some real work!