Sunday, 6 November 2016

How to execute Stored Procedure by using SQL Query and SQL Server Management Studio

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
 select @GenderCount=COUNT(*) from tblEmployee where Gender=@Gender

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.

Execute stored procedure in management studio

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

Passing input parameter using management studio

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]

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



Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive