What Are the Advantages of Passing Name-Value Pairs as Parameters in SQL Server Transact-SQL?

When calling a stored procedure defined with parameters, you can pass values to those parameters in two ways:

• Passing only values in the same order as parameters defined in the stored procedure.
• Passing name-value pairs in any order.

The advantages of passing name-value pairs to stored procedure parameters are:

• Makes the calling statement more readable - You know which value is passed to which parameter.
• Makes it possible to pass values in an order different than how parameters are defined.

The tutorial exercise shows you some good examples of passing name-value pairs as parameters:

CREATE PROCEDURE diff_in_days 
   @start_date DATETIME,
   @end_date DATETIME
AS BEGIN
   PRINT CONVERT(VARCHAR(20),@end_date,107) 
      + ' - ' 
      + CONVERT(VARCHAR(20),@start_date,107)
      + ' = ' 
      + STR(DATEDIFF(DAY, @start_date, @end_date));
END;
GO

EXEC diff_in_days 
   '01-Jan-2007', 
   '19-May-2007';
GO
May 19, 2007 - Jan 01, 2007 =        138

EXEC diff_in_days 
   @start_date='01-Jan-2007', 
   @end_date='19-May-2007';
GO
May 19, 2007 - Jan 01, 2007 =        138

-- Name-value pairs can be given in any order
EXEC diff_in_days 
   @end_date='19-May-2007',
   @start_date='01-Jan-2007'; 
GO
May 19, 2007 - Jan 01, 2007 =        138

⇒ Passing Expressions to Stored Procedure Parameters in SQL Server

⇐ Passing Values to Stored Procedure Parameters in SQL Server

⇑ Using Stored Procedures in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials