Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Passing Name-Value Pairs as Parameters

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...)

  1. What Are Stored Procedures?
  2. How To Create a Simple Stored Procedure?
  3. How To Execute a Stored Procedure?
  4. How To List All Stored Procedures in the Current Database?
  5. How To Drop an Existing Stored Procedure?
  6. How To Create a Stored Procedure with a Statement Block?
  7. How To End a Stored Procedure Properly?
  8. How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
  9. How To Get the Definition of a Stored Procedure Back?
  10. How To Modify an Existing Stored Procedure?
  11. How To Create Stored Procedures with Parameters?
  12. How To Provide Values to Stored Procedure Parameters?
  13. What Are the Advantages of Passing Name-Value Pairs as Parameters?
  14. Can You Pass Expressions to Stored Procedure Parameters?
  15. How To Provide Default Values to Stored Procedure Parameters?
  16. How To Define Output Parameters in Stored Procedures?
  17. How To Receive Output Values from Stored Procedures?
  18. How To Create a Local Temporary Stored Procedure?
  19. Can Another User Execute Your Local Temporary Stored Procedures?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...