SQL Server FAQ - Passing Name-Value Pairs as Parameters

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

(Continued from previous topic...)

What Are the Advantages of Passing Name-Value Pairs as Parameters?

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

(Continued on next topic...)