CODEWITHSUNDEEP
sql-serverstored-procedures
Mr Moose
Mr Moose

Reputation: 6344

Is there a way around SQL Servers deferred name resolution?

I just deployed a new stored proc to our test environment only to have it fail upon execution due to the fact the test system didn't contain a table that the stored proc relied upon. I believe this is due to deferred name resolution.

The thing is, I feel that at times in the past, I have attempted to create stored procs that failed due to missing dependencies. I could be wrong though.

Anyway, is it possible to somehow enforce name resolution during creation of a stored proc? If so, is there any way to get this working with sqlcmd as well as SSMS?

This way we could find out about missing dependencies upon the rollout of scripts rather upon their first execution.

On a side note, I was interested to read about this apparent deviation from the MSDN doco regarding how deferred resolution works.

Edit: We have a mix of 2005/2008 (out of my control), so I'd need a 2005 solution to work on both instances.

Upvotes: 2

Views: 3105

Answers (1)

Martin Smith
Martin Smith

Reputation: 453318

You could investigate WITH SCHEMABINDING though that may not work for you for the reasons indicated in the comments to the connect item linked to by Damien.

If on SQL Server 2008 you could also look at sys.sql_expression_dependencies

CREATE PROC bar
AS
SELECT * 
FROM DoesNotExist 
JOIN AlsoDoesNotExist ON 1=1

GO

CREATE TABLE DoesNotExist
(
X INT
)
GO

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
       referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referenced_id IS NULL

Returns

referencing_entity_name        referenced_entity_name
------------------------------ ------------------------------
bar                            AlsoDoesNotExist

Upvotes: 4

Related Questions