Create Stored Procedure with Output and Input parameters in SQL

Welcome to Quickpickdeal! In this SQL Server post, we will learn about creating and executing stored procedures with output parameters.

Before proceeding with this article, I strongly recommend reading our previous article. In that post, we discussed...

how to create stored procedures with input parameters.

In this post, we will learn how to create a stored procedure with an output parameter.

To create a stored procedure with an output parameter, we use the keywords Out or Output.

Let's consider we have a database table named Company_Customer. Now, we are going to create a stored procedure that returns the count of customers based on their salary.

CREATE TABLE [dbo].[Company_Customer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](150) NOT NULL,
    [CustomerCity] [nvarchar](500) NOT NULL,
    [CustomerSalary] [decimal](18, 2) NOT NULL,
 
) 

GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (4, 'Olivia', 'New York', CAST(1000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (5, 'Amelia', 'Paris', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1012, 'Ashok', 'New Delhi', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1014, 'George', 'Tokyo', CAST(1000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1015, 'Marry', 'Moscow', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1016, 'Alfie', 'Dubai', CAST(5000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1019, 'Neeraj Singh', 'Banglore', CAST(6000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1020, 'Oliver', 'Singapore', CAST(40000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1022, 'Noah', 'Paris', CAST(5000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1023, 'Marry', 'Barcelona', CAST(2000.00 AS Decimal(18, 2)))
GO

If you look at the stored procedure we have here, named spgetcustomercountbysalary, it has two parameters: @salary and @customercount.

Notice the difference between these two parameters: @CustomerCount has the output keyword, whereas @salary does not.

Create Procedure spgetcustomercountbysalary
@Salary decimal,
@CustomerCount int Output
as
Begin
Select @CustomerCount = COUNT(Id)
from Company_Customer
where CustomerSalary >= @Salary
End

How To Declare Input-Output Parameters Stored Procedure?

The output keyword indicates that @CustomerCount is an output parameter, while @salary is an input parameter by default because it lacks the output keyword.

Looking at the procedure definition itself, all we are doing is passing a value in the @salary parameter.

For instance, if you pass in 2000 as the salary, this query will count how many customers in the Company_Customer table have a salary greater than 2000. Then, the count obtained is used to initialize the output parameter.

How to execute stored procedure with input and output parameters in sql server?

Declare @CustomerCount int
Execute spgetcustomercountbysalary 1000, @CustomerCount output
Print @CustomerCount

To execute a procedure with an output parameter, the process is slightly different.

The procedure accepts two parameters: @salary as the input parameter and @CustomerCount as the output parameter.

An input parameter requires passing a value to the stored procedure, whereas an output parameter returns a value back to you.

When a stored procedure returns a value, you need to store it in a variable. Thus, you first create a variable to hold the value. Notice that the data type of this variable should match the data type of your output parameter, as essentially, we receive a value of this data type back.

Therefore, we create a variable named @CustomerCount of integer data type to hold the value that will come out of the stored procedure.

Then, we execute the stored procedure while passing the value for salary.

And then what does this query going to do?

The procedure counts the number of customers who have a salary greater than the parameter value and then initializes the output parameter.

Thus, we pass in a variable, @CustomerCount, which will receive that count. In this case, @CustomerCount receives the value, and finally, we print that value.