Collections:
OUTPUT - Defining Output Parameters in Stored Procedures in SQL Server
How To Define Output Parameters in Stored Procedures in SQL Server Transact-SQL?
✍: FYIcenter.com
Sometime 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.
⇒ OUTPUT - Receiving Output Values from Stored Procedures in SQL Server
⇐ Providing Default Values to Procedure Parameters in SQL Server
2016-12-28, 2377🔥, 0💬
Popular Posts:
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
Where to find SQL Server database server tutorials? Here is a collection of tutorials, tips and FAQs...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...