Yet another use of OUTER APPLY in defensive programming
Posted
by Alexander Kuznetsov
on SQL Blog
See other posts from SQL Blog
or by Alexander Kuznetsov
Published on Wed, 06 Jun 2012 21:58:00 GMT
Indexed on
2012/06/06
22:45 UTC
Read the original article
Hit count: 305
Filed under:
Defensive Programming
|Transact SQL
When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem. Prerequisites All we need is the following mock function that imitates a subquery: CREATE FUNCTION dbo.BoxById ( @BoxId INT ) RETURNS TABLE AS RETURN ( SELECT CAST ( 1 AS INT ) AS [Length] , CAST ( 2 AS INT ) AS [Width] , CAST ( 3 AS INT ) AS [Height] WHERE @BoxId = 1 ) ; Let us assume that this...(read more)
© SQL Blog or respective owner
