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
DECLARE @query NVARCHAR(MAX), @params NVARCHAR(MAX)
IF 1 = 0
-- add other tables that might be used
SELECT 1 FROM Products WHERE 1 = 0
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
The if condition is just a "Constant scan" that's never achieved so the statements inside are never executed...
If you need to work better, try working less...