Microsoft SQL Server Stored Procedures STORED PROCEDURE with OUT parameters
Example
Stored procedures can return values using the OUTPUT keyword in its parameter list.
Creating a stored procedure with a single out parameter
CREATE PROCEDURE SprocWithOutParams
(
@InParam VARCHAR(30),
@OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
SELECT @OutParam = @InParam + ' must come out'
RETURN
END
GO
Executing the stored procedure
DECLARE @OutParam VARCHAR(30)
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT
PRINT @OutParam
Creating a stored procedure with multiple out parameters
CREATE PROCEDURE SprocWithOutParams2
(
@InParam VARCHAR(30),
@OutParam VARCHAR(30) OUTPUT,
@OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
SELECT @OutParam = @InParam +' must come out'
SELECT @OutParam2 = @InParam +' must come out'
RETURN
END
GO
Executing the stored procedure
DECLARE @OutParam VARCHAR(30)
DECLARE @OutParam2 VARCHAR(30)
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT
PRINT @OutParam
PRINT @OutParam2
