Do newer versions of SQL Server (after SQL Server 2017) optimize the queries in the stored procedures that use variables instead of the so called "magic numbers"? E.g. Instead of using
SELECT ProductName, ProductDescription from Products where ProductCategory in (2, 5)
can we use
DECLARE @GADGETS_CATEGORY INT = 1 DECLARE @CLOTHES_CATEGORY INT = 5 SELECT ProductName, ProductDescription from Products where ProductCategory in (@GADGETS_CATEGORY , @CLOTHES_CATEGORY)
and still benefit from the same query optimizations?
I know there are some older questions on the topic like this one, but it would be nice to know if this has been improved.
And yes, I know that you can create a table with categories, make a join with that table and than use strings in the WHERE clause but that is not the point. Magic numbers should not exist in stored procedures. For example you should be able to write this and SQL server should be able to replace the inline constant in queries when it compiles the SP:
DECLARE @DEFINED_LIMIT_IN_MB SMALLINT = 5; SELECT ImageName, ImageDescription from Images where ImageSize < @DEFINED_LIMIT_IN_MB