Collections:
DEFAULT - Providing Default Values to Function Parameters in SQL Server
How To Provide Default Values to Function 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:
The tutorial exercise below shows you how provide default values to function parameters:
USE FyiCenterData; GO CREATE FUNCTION Age_In_Days ( @birth_date DATETIME, @today DATETIME = NULL ) RETURNS INT AS BEGIN IF @today IS NULL SET @today = GETDATE(); RETURN DATEDIFF(DAY, @birth_date, @today); END; GO -- Default value is used PRINT 'Age in days: '+STR( dbo.Age_In_Days('01-Jan-2007', DEFAULT)); GO Age in days: 138 -- Default value is not used PRINT 'Age in days: '+STR( dbo.Age_In_Days('01-Jan-2007', '11-May-2007')); GO Age in days: 130 -- Can not skip input values -- even for parameters with default values PRINT 'Age in days: '+STR( dbo.Age_In_Days('01-Jan-2007')); GO Msg 313, Level 16, State 2, Line 1 An insufficient number of arguments were supplied for the procedure or function dbo.Age_In_Days.
Â
2023-03-03, 17955👍, 1💬
Popular Posts:
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
Where to find answers to frequently asked questions on INSERT, UPDATE and DELETE Statements in MySQL...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...