MS SQL Server Interview Question:

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

Submitted by: Administrator

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

Submitted by: Administrator