I have some stored procedures that do updates using table aliases, like this:
SET ColVal = 1
FROM RealTable AS TableAlias
WHERE TableAlias.ColVal <> 1
This allows me to include joins and stuff in the update (not shown) and make it all more readable for me.
It all works fine, and the SSMS parser says it's fine. But I also have another script which looks at sys.sql_expression_dependencies and sys.objects to find stored procedures with invalid object references (see below), and it's understandably saying that all of the above type stored procedures have invalid references.
OBJECT_NAME(DEP.referencing_id) AS referencing_name,
FROM sys.sql_expression_dependencies AS DEP
-- Only validate local references:
DEP.referenced_database_name = DB_NAME()
DEP.referenced_database_name IS NULL
-- Look for references to objects that
-- don't exist in our database:
AND NOT EXISTS
SELECT NULL FROM sys.objects AS OBJ
-- Can limit objects by type if desired:
-- P: SQL_STORED_PROCEDURE
-- U: USER_TABLE
-- IF: SQL_INLINE_TABLE_VALUED_FUNCTION
-- OBJ.[type] IN ('P', 'U', 'IF') AND
-- Match on name rather than ID, because
-- referenced_id is often NULL for some reason:
OBJ.name = DEP.referenced_entity_name
ORDER BY OBJECT_NAME(DEP.referencing_id)
So I have a couple questions.
1. Is the UPDATE syntax I'm using kosher?
2. Can you recommend any updates to my stored procedure validation script that will better accommodate table aliases like mine?
"If I had been drinking out of that toilet, I might have been killed."