SQLGear

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: This can also be used while fetching values from an XML column or variable based on a node.

Mostly we write dynamic queries when we try to write the stored procedure which filters data from the column selected by the user or when the user decides on which column to sort the data or when criteria to filter data are multiple as like or equals or does not equal. Below query will give an idea on how to achieve this without writing a dynamic query.