0

I have removed a column from a table which is referred in a stored procedure. When I alter that stored procedure, there is no error, but when executing it, an error

Invalid column name

is thrown.

I'm using SSMS 2017

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
M.Tamil
  • 74
  • 4

1 Answers1

1

You are looking for functionality called "schema binding". This means that if you cannot change the schema of an object if it is being referenced in a view or stored procedure.

This is handled on the dependent level, rather than on the original object. The place to start is with the documentation on SCHEMABINDING.

In practice, this means the following procedure:

  1. Alter the object (i.e. table).
  2. Get a schema binding error.
  3. Drop the schemabinding option on all objects that refer to the original object.
  4. Modify the original object.
  5. Modify the dependent objects.
  6. Be sure schema binding is back "on" the dependent objects.

It is a bit cumbersome, but it helps enforce database dependencies.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Do we have reason to believe that the OP is using a natively compiled stored proc, the only type for which `SCHEMABINDING` actually applies? (And on which it's also mandatory so they'd already have it) – Damien_The_Unbeliever Apr 28 '20 at 15:37