How To Define Output Parameters in Stored Procedures?
Submitted by: AdministratorSometime a stored procedure not only want to take input values from the calling statement batch, but it also want to send output values back to the calling statement batch. This can be done by defining output parameters in the CREATE PROCEDURE statement.
To define an output parameter, you should use this format: "@parameter_name data_type OUTPUT", as shown in the following tutorial exercise:
DROP PROCEDURE diff_in_days;
GO
-- Defining an output parameter
CREATE PROCEDURE diff_in_days
@start_date DATETIME,
@end_date DATETIME = '19-May-2007',
@days VARCHAR(40) OUTPUT
AS BEGIN
SET @days = CONVERT(VARCHAR(20),@end_date,107)
+ ' - '
+ CONVERT(VARCHAR(20),@start_date,107)
+ ' = '
+ STR(DATEDIFF(DAY, @start_date, @end_date));
END;
GO
Command(s) completed successfully.
EXEC diff_in_days
@start_date='01-Jan-2007'
GO
Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0
Procedure or Function 'diff_in_days' expects
parameter '@days', which was not supplied.
Submitted by: Administrator
To define an output parameter, you should use this format: "@parameter_name data_type OUTPUT", as shown in the following tutorial exercise:
DROP PROCEDURE diff_in_days;
GO
-- Defining an output parameter
CREATE PROCEDURE diff_in_days
@start_date DATETIME,
@end_date DATETIME = '19-May-2007',
@days VARCHAR(40) OUTPUT
AS BEGIN
SET @days = CONVERT(VARCHAR(20),@end_date,107)
+ ' - '
+ CONVERT(VARCHAR(20),@start_date,107)
+ ' = '
+ STR(DATEDIFF(DAY, @start_date, @end_date));
END;
GO
Command(s) completed successfully.
EXEC diff_in_days
@start_date='01-Jan-2007'
GO
Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0
Procedure or Function 'diff_in_days' expects
parameter '@days', which was not supplied.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Create a Testing Table with Test Data in MS SQL Server? |
☺ | 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 in the FROM Clause in MS SQL Server? |
☺ | How To Get the Definition of a View Out of the SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |