SET versus SELECT in T-SQL

Let's start with an example:

DECLARE @SiteId INT
SELECT @SiteId=isnull(SiteId,0) FROM Orders WHERE OrderID=15
UPDATE Order_Sites SET SiteID=@SiteId

Can you spot the problem? What if SiteID in Order_Sites is not nullable? What if there is no order with OrderId 15?

That's right, when you select into a variable, you must be certain that the query returns any rows, otherwise the variable will not be set at all.

The solution is to add another operation that sets the value correctly. Here are three possible options:

• Set @SiteId to 0 before the select.
• Set @SiteId to isnull(@SiteId,0) after the select and simplify the select to not contain the isnull.
• Use the select as an argument of the isnull operation:

  SET @SiteId= isnull((select SiteId from Master_orders where OrderID=-1),0)

  Yes, you can do that.

Either way, always pay attention to this gotcha in using SQL.