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, 1352🔥, 0💬
Popular Posts:
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL? If you want ge...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...