Page 79 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

Creating Stored Procedure with Output Parameters Notes
Now we will use Output parameters in our Stored Procedure to return some value from it.
Output parameters are created in the equivalent space as the input parameters, right among the
procedure name and AS sections. The only dissimilarity is that they are specified with the word
OUTPUT immediately afterward.

Open window for New Stored Procedure and add the below code in it,
CREATE PROCEDURE Show_Customer
@FirstNo varchar(50),
@SecondNo varchar(50),
@Result varchar(50) OUTPUT
AS
SET @Result = @FirstNo + @SecondNo
Let us explain another type of example because of the reason that it is much simple to understand
how OUTPUT parameters work.

Implementing the Stored Procedure having OUTPUT parameters is very simple. Create a local
variable to hold the returned value and exhibit it. Here is the code
USE TestDB
DECLARE @res int
EXEC Show_Customer 1,3 , @res OUTPUT
PRINT @res

LOVELY PROFESSIONAL UNIVERSITY 73