Code Groups

Stored Procedure with Output parameter
You can return values from a stored procedure in three different ways.
Using SELECT statement?

Using RETURN statement

Using OUTPUT parameters

1)? Returning values Using SELECT statement

? a) Create stored procedures

Simple example here we are going to create a stored procedure that just returns a string.

CREATE PROCEDURE MyfirstsP
AS?
? SELECT ‘Welcome to the code groups’? ?
GO
The above example creates a procedure with name “MyfirstsP”? which shows the message we mentioned in line # 3.? The “GO” statement on line#4, indicates the end of the batch (and thus the end of the procedure).
How to Run the procedure ??

you need to use “EXEC ” followed by the procedure name.

EXEC MyfirstsP

2) Returning a value using Return Statement

CREATE PROCEDURE retSum
@i int
as? ?
RETURN @i+@i
GO

DECLARE @retValFromSP int

EXEC @retValFromSP = retSum 10

SELECT @retValFromSP

Return statement can return only an integer value
the execution quits unconditionally once it processes the ‘Return’ statement, so the statements after the ‘Return’ wont get executed.

3) Returning a value using OUTPUT parameters

CREATE PROCEDURE
retSumAsOutput

? @Num1 INT,

? @num2 INT,

? @Sum? INT OUT
? AS
? SELECT @sum = @Num1 + @Num2
? GO

Executing this sort of stored procedure is somewhat tricky, you need to declare the variables to hold the values returned by the stored procedure.
For example, above procedure can be executed as follows

DECLARE @Res INT
EXEC retSumAsOutput 1,2, @Res OUT
SELECT @Res

Share This