Dependency magic: Now you don't see it, Now you do

  • Article

If a user-defined function (UDF) is created prior to creating a stored procedure, object dependencies are properly listed through sp_depends. However, if you create the stored procedure first, then later create the UDF (which is allowed due to delayed name resolution), object dependencies are not displayed.

In such cases you may see the following error:
"Object does not reference any object, and no objects reference it."

To correct this issue, simply run the system stored procedure sp_refreshsqlmodule.

Below you will find an example of how to create the error followed by how to resolve it:

 --First, create a database, then a table
CREATE DATABASE ProactiveDB
GO

USE ProactiveDB
GO

CREATE TABLE dbo.CustomerInfo
(
Name varchar(25),
City varchar(25),
StateCode char(2)
)
GO

INSERT INTO dbo.CustomerInfo (Name, City, StateCode) VALUES ('ABCD', 'Charlotte', 'NC')
INSERT INTO dbo.CustomerInfo (Name, City, StateCode) VALUES ('EFGH', 'Winston Salem', 'NC')
INSERT INTO dbo.CustomerInfo (Name, City, StateCode) VALUES ('IJKL', 'Knoxville', 'TN')
INSERT INTO dbo.CustomerInfo (Name, City, StateCode) VALUES ('MNOP', 'Orlando', 'FL')
INSERT INTO dbo.CustomerInfo (Name, City, StateCode) VALUES ('QRST', 'Houston', 'TX')
GO

--Next, create the UDF, followed by the stored procedure
CREATE FUNCTION dbo.udf_StateCode (@StateCode char(2))
RETURNS tinyint
AS
BEGIN
DECLARE @returnvalue tinyint
SELECT @returnvalue = COUNT(*) FROM dbo.CustomerInfo
WHERE StateCode = @StateCode
RETURN @returnvalue
END
GO

CREATE PROCEDURE dbo.usp_CustomerStates
AS
SELECT Name, City, StateCode, dbo.udf_StateCode(StateCode) AS NumberOfCustomersInThisState
FROM dbo.CustomerInfo
GO

--Test stored procedure functionality
EXEC dbo.usp_CustomerStates

--List the dependencies of the stored procedure. 
--Notice that dbo.udf_StateCode is listed as a dependency of the stored procedure.
EXEC sp_depends 'dbo.usp_CustomerStates'

--Delete the UDF and stored procedure
DROP PROCEDURE dbo.usp_CustomerStates
DROP FUNCTION dbo.udf_StateCode
GO

--Create stored procedure first, then UDF
CREATE PROCEDURE dbo.usp_CustomerStates
AS
SELECT Name, City, StateCode, dbo.udf_StateCode(StateCode) AS NumberOfCustomersInThisState
FROM dbo.CustomerInfo
GO

CREATE FUNCTION dbo.udf_StateCode (@StateCode char(2))
RETURNS tinyint
AS
BEGIN
DECLARE @returnvalue tinyint
SELECT @returnvalue = COUNT(*) FROM dbo.CustomerInfo
WHERE StateCode = @StateCode
RETURN @returnvalue
END
GO

--Test stored procedure functionality
EXEC dbo.usp_CustomerStates

--List the dependencies of the stored procedure. 
--Notice the message "Object does not reference any object, and no objects reference it."
EXEC sp_depends 'dbo.usp_CustomerStates'

--Execute the stored procedure to correct the dependency links
EXEC sp_refreshsqlmodule 'dbo.usp_CustomerStates'

--List the dependencies of the stored procedure now.
--The correct list, including the dependency on the function, should now be listed
EXEC sp_depends 'dbo.usp_CustomerStates'