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.
2016-12-18, 1166👍, 0💬
Popular Posts:
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...
How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...
How to run Queries with "sqlcmd" tool in SQL Server? "sqlcmd" is a client tool that you can use to i...