3

I am using SQL Server 2008 and SSMS 2012. I have a stored procedure that references a table that does not exist. The editor displays red underlines on the offending table to indicate that something is wrong.

However when I execute the query, I get the message

Command(s) completed successfully.

This is extremely annoying. I also connected to the engine from another machine and it experienced the same problem, which implies its on the server, not ssms. Is there some kind of setting on the database that determines whether the database checks the syntax of stored procedures? PLEASE HELP!

Clarification: I know that the syntax is wrong. The problem is that SSMS allows me to execute the CREATE or ALTER statement without error even when it references a table that does not exist. I want it to fail. Usually it does, but for some reason it suddenly stopped giving errors. I want it to give me errors. How do I do this?

mtmurdock
  • 12,756
  • 21
  • 65
  • 108
  • 1
    Long story short, you can't. [This question](http://stackoverflow.com/questions/8713129/sql-server-avoid-deferred-compilation) covers the same issue. – Pondlife Oct 19 '12 at 20:01
  • I wrote a script to attempt compilation of stored procedures and alert us if there was a database change that broke any of them, and I find that sometimes it alerts us and sometimes it still compiles. Most curiously, we had one stored procedure which referenced a dropped table more than once, and when attempting to compile, it threw an error on ONE of the references, however after fixing that one reference it compiled without error, even though the EXACT same table was referenced in other parts of the stored procedure! Do you have any insight based on your experience with this? – Shock Feb 16 '16 at 01:38

3 Answers3

2

Your syntax is fine and that is checked when you create the stored procedure. The existence of tables is however not checked until the stored procedure is compiled and that happens when the stored procedure is executed.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    That's not true, I used to get errors that said "that object doesn't exist" all the time. – mtmurdock Oct 19 '12 at 19:50
  • 1
    I have tested this on SQL Server 2000, 2005, 2008, 2012. The behavior is the same. I can always create the stored procedure but I get an error when I try to execute the stored procedure. – Mikael Eriksson Oct 19 '12 at 19:58
  • 2
    I believe you. However, I used to get errors and I liked them. I want to get them back. It was a very useful debugging tool for me. – mtmurdock Oct 19 '12 at 20:00
0

What's going on is that the IDE in the management studio hasn't had the schema model refreshed. Since the local instance of SSMS doesn't know the table exists, it throws a redline under the table name; when you actually run the sproc/query, the code sent to the database evaluates properly and runs.

To refresh the SSMS local data, try pressing Ctrl-Shift-R, as described here.

Edit:

You might want to look into Deferred Name Resolution

Community
  • 1
  • 1
mikurski
  • 1,353
  • 7
  • 20
  • I think you're misunderstanding me. I know the table doesn't exists, and the editor IS giving me the red underline, but it still allows me to execute the ALTER or CREATE statement without error, thus committing an invalid stored procedure to the database. Ctrl+Shift+R doesn't change that. – mtmurdock Oct 19 '12 at 19:49
  • 2
    Ah, my bad. You might want to look into Deferred Name Resolution: http://msdn.microsoft.com/en-us/library/ms190686(v=sql.105).aspx – mikurski Oct 19 '12 at 20:01
  • Ok, yes this is exactly my problem. So there's no way to change this behavior? – mtmurdock Oct 19 '12 at 21:04
  • To the best of my (limited) knowledge, no. You may want to look into things like schema binding and see if that's relevant for you. – mikurski Oct 19 '12 at 21:26
0

You will not get an error message when you CREATE or ALTER, but you can check your SPs for missing dependencies with a script afterwards.

Please check my answer to the related question here (I just post a link to avoid duplication):

I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one?

Community
  • 1
  • 1
Daniel Hillebrand
  • 681
  • 9
  • 18