September 21, 2015 at 12:27 pm
Hi Gang-
I have some stored procedures that do updates using table aliases, like this:
UPDATE TableAlias
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.
SELECT
OBJECT_NAME(DEP.referencing_id) AS referencing_name,
DEP.referenced_entity_name
FROM sys.sql_expression_dependencies AS DEP
WHERE
-- Only validate local references:
(
DEP.referenced_database_name = DB_NAME()
OR
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
WHERE
-- 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
)
GROUP BY
OBJECT_NAME(DEP.referencing_id),
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?
Thanks!
September 21, 2015 at 4:35 pm
autoexcrement (9/21/2015)
I have some stored procedures that do updates using table aliases, like this:
UPDATE TableAlias
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.
That's how we do it. We predominantly use the Table Alias "U" for the table being updated, which we think helps avoid confusion / bugs (by almost always being the same Alias Name)
UPDATE U
SET ColVal = 1
FROM RealTable AS U
WHERE U.ColVal <> 1
and
UPDATE U
SET ColVal = 1
FROM RealTable AS X
JOIN OtherTable AS U
ON U.SomeID = X.SomeID
WHERE X.ColVal <> 1
September 22, 2015 at 11:03 am
I like the consistent "U" idea. Glad I'm not the only one using aliases in my update statements.
Anyone else care to chime in here? Particularly with regards to SQL Server's strange misinterpretation of the table dependencies when using this syntax?
September 22, 2015 at 1:34 pm
I dislike the consistent "U" as a table alias for the table being updated for some of the same reasons that I dislike sequential table aliases, the most important one being that I like my aliases to remind me of the underlying table and U only reminds me that the table is being updated, it doesn't remind me which table it is.
I also use SQL Prompt which can be configured to automatically add table aliases based on the table name. That also leads to consistency in table aliases while still retaining an association with the underlying table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 22, 2015 at 2:33 pm
drew.allen (9/22/2015)
I also use SQL Prompt which can be configured to automatically add table aliases based on the table name. That also leads to consistency in table aliases while still retaining an association with the underlying table.Drew
+1 on SQL Prompt... The last time I tried using SSMS w/o it, it felt like my skin was crawling... It spoils you pretty quickly.
September 22, 2015 at 5:14 pm
The referencing works just fine on 2016, so maybe it was a bug in that version?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
June 26, 2024 at 10:21 pm
Any solution for this thread ? I've faced the same situation. The only solution I found is to don't use table aliases.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply