Should you avoid mixing columns and parameter mappings in an ON JOIN statement?

Question

While trying to optimize a stored procedure using MERGE, I came across this article. How should we interpret the bold statement that it does not include comparisons with constants?

http://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx

Only specify search terms in the ON clause that define the criteria for matching data in the source and destination tables. That is, specify only the columns from the target table that are compared with the corresponding columns in the source table. Do not include comparisons with other values, such as a constant.

Does this mean that I should avoid the ON statement, which looks like this?

ON [Source].[CategoryId] = [Target].[CategoryId] AND [Source].[Color] = @Color 

Does this offer apply only to MERGE or to all types of JOINs?

Answer 1

Parameters are ok and recommended.

You should avoid connecting using conditions such as ON 1 = 1

Query performance can be improved as query compilation and recompilation rates are reduced. The query optimizer does not apply a simple parameterization process for MERGE statements. Therefore, MERGE statements that contain literal values ​​may also not work because separate INSERT, UPDATE, or DELETE statements, because a new plan is compiled every time the MERGE statement is executed.

Parameter all literals in the ON clause and in the WHEN clauses of the MERGE operator. For example, you can include the MERGE statement in a stored procedure by replacing the literal values ​​with the appropriate input parameters.