In this article, I will explain you how to execute Stored Procedure by using SQL Query and SQL Server Management Studio. Steps are given below.

Database Table :

Below is the script to create table and insert some dummy records.

create table tblEmployee
(
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NULL,
[Gender] [varchar](10) NULL
)

insert into tblEmployee values('Rahul','Male')
insert into tblEmployee values('Mary','Female')
insert into tblEmployee values('John','Male')
insert into tblEmployee values('Mathew','Male')

Stored Procedure :

Below is the stored procedure with two parameter. @Gender input parameter and @GenderCount is the output parameter which will return count of Male of Female employee. You can use OUTPUT or OUT keyword to return value.

create procedure proc_Employee
@Gender varchar(10)=null,
@GenderCount int output
as
begin
select @GenderCount=COUNT(*) from tblEmployee where Gender=@Gender
end

Executing stored procedure by using SQL Query with OUTPUT parameter :

Declare a variable of same data type as that of output parameter. Here declare a variable @GenderTotal. Now pass this variable to stored procedure with OUTPUT keyword. Here order of parameter is necessary because we have not used name of input and output parameters.

declare @GenderTotal int
execute proc_Employee 'Male',@GenderTotal output
select @GenderTotal

Order of the parameter is not necessary when we pass parameter names.

declare @GenderTotal int
execute proc_Employee @GenderCount=@GenderTotal output,@Gender='Female'
select @GenderTotal

Executing stored procedure by using SQL Server Management Studio :

To Execute Stored Procedure with SSMS, go to your Database, expand Database folder → expand Programmability folder → expand Stored Procedures → now right click on your SP as shown below.

Now click on the Execute Stored Procedure.., a new window will open as shown below.

Now, pass the value for @Gender parameter as Male or Female and click on OK, you will see the GenderCount as output. SQL-Server generates a query when you execute the SP through Management Studio. You can modify as per your need. Below is the query generated.

USE [Articles]
GO

DECLARE    @return_value int,
        @GenderCount int

EXEC    @return_value = [dbo].[proc_Employee]
        @Gender = N'Male',
        @GenderCount = @GenderCount OUTPUT

SELECT    @GenderCount as N'@GenderCount'

SELECT    'Return Value' = @return_value

GO