Create Stored procedure with Output and Input parameters in SQL

Welcome to Appsloveworld, In this Post of SQL Server, we will learn to creating and executing stored procedures with output parameters.

Now, before reading this article, I strongly recommend reading our last article.In this post, we have 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.

For creating a stored procedure with an output parameter. We use the keywords Out or Output.

we have a database table Company_Customer. Now we are about to create a stored procedure which gives us the customer count by 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 stored procedure that we have here,spgetcustomercountbysalary,this procedure has got two parameters @salary and @customercount.

And if you look at the differences between these two parameters @CustomerCount has got output Keywood, whereas @salary doesn’t have it.

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?

Output keyword indicates that this @CustomerCount is an output parameter and @salary is an input parameter, by default because it doesn’t have the output keyword.

if you look at the procedure definition itself, all we are doing is, we are passing value in the @salary parameter.

For example, if you pass in 2000, as the salary, what this query is going to do is it’s going to count, from Company_Customer table how many customers are there, who’s salary is greater then 2000. And then whatever count we’re getting, we’re using that count to initialize this 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 the output parameter. It’s slightly different.

You know that whole procedure is accepting two parameters,First is the @salary parameter and the other one is the @CustomerCount. @salary is an input parameter and @CustomerCount is an output parameter.

Now obviously input parameter means you have to pass something to the stored procedure, whereas the output parameter will return something back to you.

Now when a stored procedure returns something back to you, you have to hold it somewhere obviously in a variable.

That’s why you first create a variable to receive the value. So I have created a variable called @CustomerCount and notice the data type of this the variable should match the data type of your output parameter because essentially we get a value of this data type back.

So if we need a variable, to hold the value that is going to come out of the stored procedure.So that’s why we are creating a variable of an integer data type.

And then when we actually execute the stored procedure.and passing the value for salary.

And then what does this query going to do?

It’s going to count the number of customers who have a salary greater than the parameter value and then it’s going to initialize the output parameter.

so we are passing in a variable which is going to receive that count in this case @CustomerCount receives the value and then finally we print that value.

The post Create Stored procedure with Output and Input parameters in SQL appeared first on Software Development | Programming Tutorials.