How To Provide Default Values to Stored Procedure 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 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:
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
Submitted by: Administrator
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:
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
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Use Subqueries with the EXISTS Operators in MS SQL Server? |
☺ | How To Get the Definition of a View Out of the SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |