In this article I will explain with an example, how to execute (run) Stored Procedure in SQL Server Management Studio.Studio.
The Stored Procedure can be easily called (executed) using the EXEC command in SQL Server Management Studio.
This method works for all versions for SQL Server Management Studio editions i.e. 2000, 2005, 2008, 2008R2, 2012, 2014 or higher including SQL Server Management Studio Express editions.
 
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Download and install Northwind Database
 
 
Stored Procedure that returns value
In order to illustrate the process of assigning result value EXEC function to Variable in SQL Server, the following Stored Procedure is used which returns an Integer value 1 if the EmployeeId exists and 0 if the EmployeeId does not exists.
CREATE PROCEDURE CheckEmployeeId
      @EmployeeId INT
AS
BEGIN
      SET NOCOUNT ON;
 
      DECLARE @Exists INT
 
      IF EXISTS(SELECT EmployeeId
                        FROM Employees
                        WHERE EmployeeId = @EmployeeId)
      BEGIN
            SET @Exists = 1
      END
      ELSE
      BEGIN
            SET @Exists = 0
      END
 
      RETURN @Exists
END
 
 
 
Executing Stored Procedure in SQL Server Management Studio
In order to fetch the returned integer value from the Stored Procedure, you need to make use of an Integer variable and use along with the EXEC command while executing the Stored Procedure.
Syntax
Return Value from Stored Procedure in SQL Server example
Example
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckEmployeeId 1
SELECT @ReturnValue
 
Output
Valid EmployeeId
Execute (Run) Stored Procedure in SQL Server Management Studio
Invalid EmployeeId
Execute (Run) Stored Procedure in SQL Server Management Studio
 
Downloads

CheckEmployeeId.sql