Introduction

In this article, we will learn how to use a Stored Procedure in SQL.

The SQL query can be saved as a Stored Procedure, and then just call it to execute it. So the code can be used over and over again.

You can also pass parameters to a Stored Procedure so that the Stored Procedure can act based on the passed parameter value(s).

Syntax

• Create a Stored Procedure

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

• Execute a Stored Procedure

EXEC procedure_name;

Stored Procedure Without Parameters

• Create a Stored Procedure

The subsequent statement would create a stored procedure named “SP_GetAllArticles” that selects all records from the “Article” table:

CREATE PROCEDURE SP_GetAllArticles
AS
SELECT * FROM Article
GO;

• Execute a Stored Procedure

The subsequent statement would execute the stored procedure named “SP_GetAllArticles“.

EXEC SP_GetAllArticles;

Stored Procedure With Parameters

Setting up multiple parameters is very easy. As shown below, just list each parameter and the data type separated by a comma.

• Create a Stored Procedure

The subsequent statement would create a stored procedure that selects Articles of a particular Author with a particular Title from the “Article” table:

CREATE PROCEDURE SP_GetArticles @Author nvarchar(50), @Title nvarchar(50)
AS
SELECT * FROM Article WHERE Author = @Author AND Title = @Title
GO;

• Execute a Stored Procedure

The subsequent statement would execute the stored procedure named “SP_GetArticles“.

EXEC SP_GetArticles @Author = "Yasin Panwala", @Title = "How To Write Select Query In SQL";

Also, check How To Delete Duplicate Records In SQL