How to check that stored procedure is valid

Question

As we know, it is possible to compile and to have so stored procedures:

CREATE PROC dbo.MyProc1
AS
  SELECT * FROM Not_Exists_Or_Removed_Table

or

CREATE PROC dbo.MyProc2
AS
  SELECT 
   Id
   ,Removed_Column
 FROM MyValidTable

The table Not_Exists_Or_Removed_Table is not exists or was removed (MyProc1) and column Removed_Column was removed (MyProc2).

How to detect and prevent creating so stored procedures?

Answer 1

According to the MSDN article - Deferred Name Resolution and Compilation only the first situation is working, second is not: "Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table."

For checking this kind of situations, I'd use a database project in Visual Studio with automatic (daily) deploy and verification of all warnings (maybe treat warning as errors), because these issues would be warnings, not errors.

Answer 2

You can prevent creating a procedure with invalid objects before executing the Create Procedure statement like that:

You will see a red line under the invalid object which underlines the error when you go with the mouse over the red line. I am not aware of other method, because the SQL will parse the procedure before executing it considering only the validity of the SQL syntax.