Microsoft SQL Server 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