MS SQL Server Concepts and Programming Question:
Download Questions PDF

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

Answer:

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


Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Provide Values to Stored Procedure Parameters in MS SQL Server?Can You Pass Expressions to Stored Procedure Parameters?