Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Provide Default Values to Function Parameters?
Submitted by: AdministratorIf 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 define a default value to a parameter when creating the function, you should use this format: "@parameter_name data_type = default_value".
* To use the default value of a parameter when executing the function, you should use the keyword DEFAULT as the input value for that parameter.
The tutorial exercise below shows you how provide default values to function parameters:
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 in
Submitted by: Administrator
* To define a default value to a parameter when creating the function, you should use this format: "@parameter_name data_type = default_value".
* To use the default value of a parameter when executing the function, you should use the keyword DEFAULT as the input value for that parameter.
The tutorial exercise below shows you how provide default values to function parameters:
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 in
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.