Blog ala Vidar

A couple of days ago Torgeir came over to my desk and told me proud “you shouldn’t use SELECT when setting variables. You should use SET”. Then he told me the reason, and it got me thinking.. The reason is simple. If you do this:

DECLARE @PrimKey AS UNIQUEIDENTIFIER
SELECT @PrimKey = PrimKey
	FROM stbl_WinClient_Projects
	WHERE ProjectName = 'Sys.Security.ManageUsers'
PRINT @PrimKey
SELECT @PrimKey = PrimKey
	FROM stbl_WinClient_Projects
	WHERE ProjectName = 'A.Project.That.Does.Not.Exist'
PRINT @PrimKey

Notice that it will return the same PrimKey both times. When you’re writing the query, you might think that this will give you NULL on the last SELECT, but it won’t because there’s no records returned, and it will therefore not touch the variable. The correct way of doing this would then be:

DECLARE @PrimKey AS UNIQUEIDENTIFIER
SET @PrimKey = (SELECT PrimKey
		FROM stbl_WinClient_Projects
		WHERE ProjectName = 'Sys.Security.ManageUsers')
PRINT @PrimKey
SET @PrimKey = (SELECT PrimKey
		FROM stbl_WinClient_Projects
		WHERE ProjectName = 'A.Project.That.Does.Not.Exist')
PRINT @PrimKey

In some cases you might want to add ISNULL. For instance in subselects, this might be important. In the next query you’ll notice what I’m talking about. The first column will return NULL, and the other will return 0.000000:

SELECT
  (SELECT TOP 1 ISNULL(Amount, 0) AS Amount
	FROM atbv_Accounting_Transactions
	WHERE Series = 'SomethingThatDoNotExists'
	ORDER BY Period DESC) AS Amount1,
  ISNULL((SELECT TOP 1 Amount AS Amount
	FROM atbv_Accounting_Transactions
	WHERE Series = 'SomethingThatDoNotExists'
	ORDER BY Period DESC), 0) AS Amount2

Both queries are using ISNULL, but the first is using INSIDE the subquery, but since this will never return any records, it will never come that far.. The second uses ISNULL OUTSIDE the subquery, so if the subquery returns NULL, it will replace it with 0.0000

NB! One last thing… Some bright souls may have noticed that I haven’t used   NOLOCK on the first queries. I’ll send you a price. Good boy/girl 😀 ALWAYS REMEMBER TO USE WITH (NOLOCK) WHEN SELECTING FROM TABLES!