question

sreeraj avatar image
sreeraj asked

how can I turn deferred name resolution off? I don't want my stored procedures to compile if a table it references does not exist. Is it possible?

i dont want my stored procedure to compile if a table it references does not exists.

sqlserver2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

No you cannot turn off deferred name resolution.

What issue is this causing you?

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I am not facing any issue. But i want to found the non-existence object in procedure during complile time itself.

0 Likes 0 ·

is there any workaorund?

0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

The workaround would be to run the stored procedure (inside a transaction if it's a proc which will change stuff).

I think even getting the estimated execution plan for the exec-statement for the procedure will spot the error, because then you'll get a compilation error at statement-level. But I'm not 100% sure about that, and if your stored procedure uses temporary tables, it won't work to get the estimated execution plan anyway, then your only option would be to execute the proc.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

We had a question discussing workarounds previously (I don't believe you can account for temp tables or dynamic sql, so actual execution is still the only sure way to do it) - https://ask.sqlservercentral.com/questions/2700/how-to-check-invalid-objects-in-database.html

one of the accepted answers include a loop to check all procedures in a single pass

1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.