How To Execute A SQL Stored Procedure | SSMS GUI & T-SQL

Guides / By

Running a stored procedure in SQL Server takes one command — EXECUTE — in SSMS, or just a few clicks in Object Explorer.

Stored procedures are the workhorses of SQL Server: precompiled batches of T-SQL logic that accept parameters, return results, and keep your code organized. Whether you are a DBA automating maintenance or a developer pulling data for a report, the execution method you choose — query window or GUI — depends on whether you are scripting for reuse or running a one-off. Both start in SQL Server Management Studio (SSMS 20.17+), and both need the same prerequisite: a procedure that already exists and the right permission to run it.

What You Need Before Running a Stored Procedure

Three things must be in place before you can execute any stored procedure. First, SSMS must be installed on a Windows 10 or Windows 11 machine and connected to your SQL Server instance (on-premises or Azure SQL Database). Second, the procedure must have been created with CREATE PROCEDURE — you cannot execute something that has not been compiled into the database. Third, your SQL Server login must have the EXECUTE permission on that procedure; without it, the engine returns a permissions error even when the procedure name is correct.

Executing a SQL Stored Procedure: The EXEC Syntax That Works

The most reliable way to run a stored procedure across every SQL Server edition — Express, Standard, or Enterprise — is the EXECUTE command (or its shorthand EXEC). Unlike the right-click GUI method, this approach never changes between SSMS versions and works identically in SQL Server 2019, 2022, and Azure SQL Database. The core syntax is straightforward: EXECUTE ProcedureName; when the procedure takes no parameters, and EXECUTE ProcedureName N'value1', N'value2'; when it does.

Method 1: EXECUTE a Stored Procedure With T‑SQL

Open a new query window in SSMS (New Query from the toolbar), type your EXECUTE statement, and press Execute. This method gives you full control over parameters, lets you script the call for later reuse, and works in automated jobs and deployment scripts.

No‑parameter call: The simplest case needs no arguments. EXECUTE sp_who; returns active user sessions immediately. If the statement is the first in its batch, you can drop EXECUTE entirely — sp_who; alone works.

Passing parameters by position: List the values in the order they appear in the procedure definition, separated by commas and enclosed in single quotes for string types.

EXECUTE uspGetEmployeeDetails N'Smith', N'Accounting';
GO

Passing parameters by name: Explicitly naming each parameter makes the call self‑documenting and order‑independent.

EXECUTE uspGetEmployeeDetails @LastName = N'Smith', @Department = N'Accounting';
GO

Handling output parameters: Use the OUTPUT keyword both in the procedure definition and in the execution call so the engine returns the value to a variable.

DECLARE @EmployeeCount INT;
EXECUTE uspCountByDepartment @Department = N'Accounting', @Count = @EmployeeCount OUTPUT;
PRINT @EmployeeCount;

Method 2: Execute a Stored Procedure From the SSMS GUI

For one‑off executions or when you are still learning the parameter list, the Object Explorer context menu provides a visual parameter dialog. In Object Explorer, expand Databases → your target database → Programmability → Stored Procedures. Right‑click the procedure and choose Execute Stored Procedure. The dialog shows each parameter, its data type, and a column for the value. Fill in the values (or check Pass Null Value for nullable parameters), then click OK. SSMS generates and runs the T‑SQL behind the scenes, displaying the results in the query results pane. This method is slower for repetitive work but invaluable when you need to inspect a procedure’s parameter signatures without reading the CREATE script.

Factor GUI (Object Explorer) T‑SQL (EXECUTE)
Learning curve Zero — right‑click and fill in fields Requires knowing parameter names and syntax
Speed for repetitive calls Slow — each call requires manual clicks Fast — paste and run or script it
Best for One‑off execution, exploring unfamiliar procedures Automation, scripting, production deployments
Scriptable No — generates T‑SQL behind the scenes but does not save it Yes — paste into jobs, deployment scripts, or CI/CD
Remote execution Works with any connected instance Works with any connected instance; supports linked servers
Error handling Displays errors in the results grid Supports TRY…CATCH blocks and PRINT statements
Supports output parameters Yes — dialog shows output parameters and captures them Yes — requires DECLARE + OUTPUT keyword

Setting a Stored Procedure To Auto‑Execute at Startup

SQL Server can run a stored procedure automatically every time the sqlservr service starts. This is useful for session‑level configuration or logging. Use the system procedure sp_procoption to mark any procedure for startup execution.

EXEC sp_procoption @ProcName = N'uspLogStartup',
                   @OptionName = 'startup',
                   @OptionValue = 'on';
GO

Disable it by setting @OptionValue = 'off'. Only procedures in the master database can be marked for auto‑execution, and they must have been created with CREATE PROCEDURE in master first. The feature is supported in all SQL Server editions but requires sysadmin privileges to configure.

What Are the Most Common Execution Mistakes?

Even experienced developers trip on a few recurring pitfalls when calling stored procedures. The table below covers the ones that produce the most confusing errors.

Mistake What Happens Fix
Missing commas between parameters EXEC proc N'val1' N'val2' — syntax error Add commas: EXEC proc N'val1', N'val2'
Omitting required parameters Procedure expects parameter @Dept which was not supplied Provide a value for every parameter that lacks a default
Using CALL instead of EXEC CALL is not recognized — syntax error Use EXEC or EXECUTE (CALL is for MySQL)
Running EXEC when not first in batch May produce Incorrect syntax depending on context Use EXECUTE (the full keyword) inside a batch
Passing wrong parameter name EXEC proc @WrongName = N'val' — parameter not found Verify the exact @ParamName in the procedure definition
No EXECUTE permission EXECUTE permission denied on object Grant permission: GRANT EXECUTE ON proc TO user;
Dynamic SQL injection risk EXECUTE ('SELECT ...') with unvalidated user input Use sp_executesql with parameterized queries instead

Executing on Azure SQL and Remote Servers

The EXECUTE syntax works identically on Azure SQL Database — no special adjustments needed. For remote SQL Server instances, ensure TCP port 1433 is open in the firewall and that your connection string references the correct server and database. Linked server calls use a four‑part name: EXECUTE [LinkedServer].[Database].[Schema].[Procedure];. Output parameters and error handling behave the same way regardless of whether the target is local or cloud.

Whichever method you choose — the precision of T‑SQL or the convenience of the GUI — the core command is the same: EXECUTE. Master that keyword, and you can run any stored procedure on any SQL Server instance without hesitation.

References & Sources

Please use a real email you check. If it's fake or mistyped, your message won't reach us and we can't reply — wrong addresses are rejected automatically.