Assigning values to variables from same table but different rows
I often see developers assigning values to variables from the same table but different rows based on the row ID. The values are mostly from the same column. What I see is the table is queries multiple times and could impact performance. Below is the sample query and its execution plan.
DECLARE @Method1 nvarchar(50);
DECLARE @Method2 nvarchar(50);
SET @Method1=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=1);
SET @Method2=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=2);
Instead, we can do it as in below query:
DECLARE @Method1 nvarchar(50);
DECLARE @Method2 nvarchar(50);
SET @Method1=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=1);
SET @Method2=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=2);
Instead, we can do it as in below query:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @Method1 nvarchar(50) | |
| DECLARE @Method2 nvarchar(50) | |
| SELECT @Method1=CASE DeliveryMethodID WHEN 1 THEN DeliveryMethodName ELSE @Method1 END, | |
| @Method2=CASE DeliveryMethodID WHEN 2 THEN DeliveryMethodName ELSE @Method2 END | |
| FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID IN (1,2); |
This can also be used while fetching values from an XML column or variable based on a node.
Make money by filling online surveys
Comments