Collections:
Providing Default Values to Procedure Parameters in SQL Server
How To Provide Default Values to Stored Procedure Parameters in SQL Server Transact-SQL?
✍: FYIcenter.com
If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter.
To provide a default value to a parameter, you should use this format: "@parameter_name data_type = default_value". The tutorial exercise below shows you how provide default values to stored procedure parameters:
USE FyiCenterData; GO DROP PROCEDURE diff_in_days; GO CREATE PROCEDURE diff_in_days @start_date DATETIME, @end_date DATETIME = '19-May-2007' AS BEGIN PRINT CONVERT(VARCHAR(20),@end_date,107) + ' - ' + CONVERT(VARCHAR(20),@start_date,107) + ' = ' + STR(DATEDIFF(DAY, @start_date, @end_date)); END; GO -- Default value is used EXEC diff_in_days @start_date='01-Jan-2007'; GO May 19, 2007 - Jan 01, 2007 = 138 -- Default value is not used EXEC diff_in_days @start_date='01-Jan-2007', @end_date='11-May-2007'; GO May 11, 2007 - Jan 01, 2007 = 130 -- Input value must be supplied for a parameter -- without a default value EXEC diff_in_days @end_date='11-May-2007'; GO Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0 Procedure or Function 'diff_in_days' expects parameter '@start_date', which was not supplied.
⇒ OUTPUT - Defining Output Parameters in Stored Procedures in SQL Server
⇐ Passing Expressions to Stored Procedure Parameters in SQL Server
2016-12-28, 1459🔥, 0💬
Popular Posts:
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
Where to find tutorials to answer some frequently asked questions on Microsoft SQL Server Transact-S...