October 31, 2012 at 4:35 am
Hi,
When writing a procedure using dynamic SQL (cause of parameters that may not be used, ...) SQL Server doesn't "fill" the sys.sql_dependencies cause it doesn't know what tables are used (the query is a string....).
I don't know if its important for SQL Server to know the dependencies correctly but if they exist there's a reason :-), even if just for when dropping an object warn what other objects use it...
So I wonder if it's a good approach to add at the begging of the procedure, triggers, ... an IF condition that's never true (1 = 0) and use the tables involved on the dynamic SQL?
CREATE PROCEDURE GetProducts
@ID INT = NULL,
@Name VARCHAR(10) = NULL
AS
BEGIN
DECLARE @query NVARCHAR(MAX), @params NVARCHAR(MAX)
IF 1 = 0
BEGIN
-- add other tables that might be used
SELECT 1 FROM Products WHERE 1 = 0
END
SET @params = '@IdIn INT, @NameIn VARCHAR(10)'
SET @query = 'SELECT * FROM dbo.Products WHERE 1 = 1'
IF @ID IS NOT NULL SET @query = @query + ' AND ProductID = @IdIn'
IF @Name IS NOT NULL SET @query = @query + ' AND ProductName = @NameIn'
EXEC sp_executesql @query, @params, @IdIn = @Id, @NameIn = @Name
END
The if condition is just a "Constant scan" that's never achieved so the statements inside are never executed...
Thanks,
Pedro
October 31, 2012 at 7:44 am
No. Just no.
There's a million ways to mess up dependencies and sys.sql_dependencies is notorious for being wrong. The improved DMVs in 2008 and above are far better, still not perfect (dynamic SQL). Don't make your code more complex for no good reason
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2012 at 8:28 am
Hi,
Just tried the sys.dm_sql_referenced_entities but the dynamic SQL isn't analyzed...
The following code returns info for the "normal" GetProducts that uses ProductId = COALESCE(@ID, ProductId) AND ... but as you say in a post using dynamic SQL is best for these situations: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('dbo.GetProducts2', 'OBJECT')
I was just trying a way of getting the dependencies correct but with dynSQL there isn't much chance on doing it... or I just don't know how
Thanks,
Pedro
October 31, 2012 at 9:34 am
PiMané (10/31/2012)
Hi,Just tried the sys.dm_sql_referenced_entities but the dynamic SQL isn't analyzed...
The following code returns info for the "normal" GetProducts that uses ProductId = COALESCE(@ID, ProductId) AND ... but as you say in a post using dynamic SQL is best for these situations: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('dbo.GetProducts2', 'OBJECT')
I was just trying a way of getting the dependencies correct but with dynSQL there isn't much chance on doing it... or I just don't know how
Thanks,
Pedro
Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?
October 31, 2012 at 9:42 am
Correct, you can't get dependencies in any automated way with dynamic SQL. That's one of its downsides.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2012 at 9:48 am
patrickmcginnis59 (10/31/2012)
Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?
Well.. when someone like Gail says "No. Just no." you just have "to go with it" 🙂
I'm just updating my skills since I stopped "real" DBA for 3 years...
But when someone like Gail or Grant or Glenn says something like that you don't question it. Not a sheep following the herd blindly but just learning with the best...
Pedro
October 31, 2012 at 10:04 am
Basically it's additional complexity in the procedures solely for correct dependencies.
Do you use those dependencies in your development? Do you have any process or procedure that requires that the dependencies be absolutely correct? Do you gain anything at all for this additional complexity?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2012 at 10:20 am
GilaMonster (10/31/2012)
Basically it's additional complexity in the procedures solely for correct dependencies.Do you use those dependencies in your development? Do you have any process or procedure that requires that the dependencies be absolutely correct? Do you gain anything at all for this additional complexity?
In my case I'd need it to build a dependency diagram for all the database objects.
Probably, for now, I'll build the possible diagram from the dependencies and add a '?' whenever there's an exec sp_executesql...
Thanks,
Pedro
October 31, 2012 at 11:15 am
PiMané (10/31/2012)
patrickmcginnis59 (10/31/2012)
Did you ever figure out the drawback to your strategy? It seemed to make sense to me. What is the downside?Well.. when someone like Gail says "No. Just no." you just have "to go with it" 🙂
LOL duly noted!
February 13, 2013 at 7:44 pm
I realise this is an old post, but I came across it when trying to look for a way to find dependencies within dynamic SQL. One thing I saw suggested on this stackoverflow post was a the free SQL Search tool from RedGate.
I think when used in conjunction with the DMVs listed above, it can certainly help you find your dependencies.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply