Dynamic SQL and Object Dependency

One of the most powerful features in MSSQL SERVER is dynamic sql that has its own complexity. When we use this feature, it’s very important that we must know its limitations and correct usage of it. By design, we limit in identify dependencies that invoked from Dynamic SQL.

Sample:

--drop proc if exists
IF OBJECT_ID('dbo.proc_1') > 0
DROP PROC dbo.proc_1 ;
go
--create proc_1
CREATE PROC dbo.proc_1
AS
DECLARE @sql NVARCHAR(MAX) = 'exec dbo.proc_2;' ;
--first call with sp_executesql (standard call mode)
EXEC sp_executesql @sql ;
--secound call with exec() (legacy call mode)
EXEC (@sql) ;
GO
--drop proc if exists
IF OBJECT_ID('dbo.proc_2') > 0
DROP PROC dbo.proc_2 ;
GO
--create proc_2
CREATE PROC dbo.proc_2
AS
SELECT 2 AS proc_2 ;
GO

Now check these stored procedures with sp_depends or from SSMS. Even sp_refreshsqlmodule cannot help us.


--refresh sql module for proc_1
EXEC sp_refreshsqlmodule 'proc_1';
-- call sp_depends for proc_1
EXEC sp_depends 'proc_1';
--refresh sql module for proc_2
EXEC sp_refreshsqlmodule 'proc_2';
-- call sp_depends for proc_2
EXEC sp_depends 'proc_2';

Your result must be like this:

Object does not reference any object, and no objects reference it.

Advertisements
This entry was posted in Joy of T-SQL, T-SQL Tips & Tricks. Bookmark the permalink.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s