Create Stored Procedure in SQL Server

In this SQL Server tutorial, you will learn how to create stored procedure in SQL Server.

Where I will explain the basic syntax for creating a store procedure and why we need to create a stored procedure in Microsoft SQL server.

After that, you will understand with an example of how to create, view, and call the stored procedure.

Table of Contents

Create Stored Procedure in SQL Server

First, you need to know ‘What is the stored procedure in SQL Server?’. SQL stored procedure is a precompiled or prepared set of one or more SQL statements that you can execute as a single unit.

In other words, you can consider a stored procedure as a kind of function in other programming languages that contains several SQL statements, and when you call this function, it performs specific actions.

But why create a stored procedure, the stored procedure is created to perform the task that you do again and again, or for complex tasks such as retrieving data, updating data, and data manipulation.

Stored procedures are used for performance, security, modularity, etc. Well, you know about stored procedure.

Let’s understand how to create a stored procedure using the syntax.

CREATE PROCEDURE name_of_procedure
AS
BEGIN  
 ----- Here write your SQL statement

END;

Where,

CREATE PROCEDURE: It is the statement that creates a stored procedure.
name_of_procedure: Name of the stored procedure that you want to create. Specify the name of the procedure after the CREATE PROCEDURE statement.
AS: This keyword indicates the beginning of the store procedure.
BEGIN and END: All the SQL statements that you want to execute are written between BEGIN and END blocks.

The above is the basic syntax to create a stored procedure, you can also create a store procedure that accepts a parameter for a more complex task.

Now, you have a Products table with columns ProductID, ProductName, Price, Description, and Stock, which is shown below.

Create Stored Procedure in SQL Server Table Products

For example, every time you need to write the ‘SELECT * FROM products’ to view the Products table, but now you want to create a stored procedure that does the same work.

So you can use the below query to create a stored procedure that will show the Products table information.

CREATE PROCEDURE Products_Info
AS
BEGIN
	SELECT * FROM products;
END;

When you execute the above query, a new stored procedure named ‘Products_Info’ is created in your database on your SQL Server instance.

If you want to view the created stored procedure, use the below query.

SELECT name FROM sys.procedures;

After the execution of the above query, it shows the stored procedure in your database which is Products_Info as you can see in the above output.

So whenever you create a new stored procedure, it is saved in your database in the sys.procedures system catalog view.

Till now you know how to create and view the stored procedure; let’s learn how to call or execute the stored procedure.

To call the stored procedure in SQL Server use the below query.

EXECUTE procedure_name;
                   OR
EXEC procedure_name;

Two statement EXECUTE and EXEC is used to call the stored procedure in SQL Server.

Now call the Products_Info stored procedure using the below query.

EXECUTE Products_Info;

When you execute the Products_Info stored procedure, it shows the Products table information that you can see in the above output.

This time, you haven’t written any select statement; instead, you called the stored procedure and got the same result when you executed ‘SELECT * FROM products’ in the beginning.

So when you have complex queries and need to write the same query repeatedly, you can create a stored procedure containing the queries or statements. Now I hope that you have a clear understanding of creating stored procedures in SQL Server.

Conclusion

In this SQL Server, you learn how to create stored procedure in SQL Server. First learned about the concept of stored procedure. Then the syntax of the stored procedure and finally created a store procedure; also learned how to view and call the stored procedure.