Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
You are right, in second case I cannot do that (create). But I can remove column and stored procedure will be invalid. –  garik Mar 29 '11 at 9:16
add comment

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.

share|improve this answer
    
This helper (prompt) does not work properly. Sometimes it underlines on valid objects (a new ones), sometimes it does on invalid, so in many cases I ignore this helper. Thanks, it is better then nothing, of course. –  garik Mar 29 '11 at 9:13
    
Yes you're right. You have to refresh the IntelliSense from Edit -> IntelliSense -> Refresh Local Cache or pres CTRL+SHIT+R. Then you will have the new tables created validated and only the invalid tables will have the red line. –  yrushka Mar 29 '11 at 10:29
    
+1 thanks for shortcut! –  garik Mar 29 '11 at 11:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.