What is a stored procedure in SQL Server?

Microsoft uses TSQL (or Transact-SQL), a variant of SQL with some advanced extensions, as the query language in their SQL Server database. Stored procedure is one of those very important extensions of TSQL.

It allows you to group several SQL queries into a pack, stored it on the server so it can be used over and over again. The next time you need it, just call and run it without hanging to write again.

Because stored procedure stores the code on the server instead of locally on the client, it has several benefits:

It reduces the repetitive work. You write it once, SQL Server saves it so you can quickly call and execute it anytime you want.
It allows faster execution. Because it’s parsed, optimized and stored in memory after you execute it for the first time. The next time you call it, SQL Server run it faster.
It reduces network communication cost between the server and client. Each time you call it from the client, a single line of code is sent to the server instead of hundreds of lines over the network.
It powers dynamic SQL as a security mechanism to prevent SQL Injection.
You can also grant permission to run a stored procedure to a user with no permission to run each query of the stored procedure separately.

How to write a stored procedure in SQL Server?

Here is the general syntax of a stored procedure:

CREATE PROCEDURE procedure_name
AS
sql_statement(s)
GO;

After you created a stored procedure, you can call and execute it:

EXEC procedure_name;

Let’s take a look at some examples of using Stored Procedure in SQL Server.

1. Stored procedure with no parameter:

CREATE PROCEDURE getallmetrics
AS
SELECT * FROM daily_active
SELECT * FROM weekly_active
SELECT * FROM monthly_active
GO;

Now execute it:

EXEC getallmetrics;

2. Stored procedure with one parameter

CREATE PROCEDURE getorder @order_id int4
AS
SELECT * FROM orders WHERE order_id = @order_id
GO;

Now execute it:

EXEC getorder order_id = "454";

3. Stored procedure with multiple parameters

CREATE PROCEDURE getbigorder @order_value int, @payment_method nvarchar(10)
AS
SELECT * FROM orders 
WHERE order_value = @order_value 
AND payment_method = @payment_method
GO;

Now execute it:

EXEC getbigorder order_value = "1000", payment_method = "PayPal";

Need a good GUI Client to work with MS SQL Server? TablePlus provides a modern, native tool with intuitive UI to manage multiple relational databases in cluding SQL Server, MySQL, PostgreSQL, SQLite, Oracle… And it’s available for free!