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 can also be used while fetching values from an XML column or variable based on a node.
Make money by filling online surveys
Comments