Today in this article, we will see how to use SQL Stored Procedure with examples.

Stored procedures are SQL statements that consist of commands or code generally in the form of functions that can take inputs and execute the logic on the server side for the given conditions.

Today in this article, we will cover below aspects,

There are multiple benefits of SQL Stored procedure like,

  • Centralize the logic on the server
  • Reduced client network traffic
  • Reuse of code
  • Save time and memory
  • Improved performance

SQL Stored Procedure Naming Pattern

Naming Syntax Pattern

CREATE PROCEDURE [SP_Name]
AS
BEGIN
[Write SQL statement]
END

Writing simple SQL Stored procedure

Let’s try to write a simple stored procedure.

Step 1 – Login to SQL Database.

Step 2- Write the SQL procedure as below.

This Stored procedure return list of all employees from the Database.

Example 1

CREATE PROCEDURE GetEmployess
AS
BEGIN

select * from Employee


END

httpswwwthecodebuzzcomsql stored procedure with examplesopens in a new tab

Once executed above stored procedure will be listed under the Stored procedure section.

SQL Stored Procedure

Step3Execute SQL Stored Procedure

Naming Syntax

EXEC <Stored Procedure Name> <@Input>

Example

Exec GetEmployees

SQL Stored Procedure

Writing a Stored procedure with Single or Multiple Parameters

Writing Stored procedure with single or multiple Parameters as below,

Naming Syntax

CREATE PROCEDURE <SP Name> @<Input1> @<Input2> .. 
AS
BEGIN
select * from <Table Name> where <ColumnName>= @Input1....
END

Example

Below stored procedures return the list based on matching FirstName

CREATE PROCEDURE GetEmpList @FirstName nvarchar(30)
AS
BEGIN
select * from Employee where FirstName = @FirstName
END

Executing the above-stored procedure return the below result,

EXEC GetEmpList @FirstName = ‘TheCodeBuzz’

blank

Stored Procedure With Multiple Parameters

Example

Below Stored procedures return the result for matching FirstName and LastName

CREATE PROCEDURE GetEmployeeList @FirstName nvarchar(30), @LastName nvarchar(30)
AS
BEGIN
select * from Employee where FirstName = @FirstName AND LastName =@LastName
END

Let’s execute the above-stored procedure,

EXEC GetEmployeeList @FirstName = ‘TheCodeBuzz’ , @LastName = ‘Test’

SQL Stored Procedure example

References:

That’s All!

This was very basic we covered today. Hope this helps you get started if you are new to the SQL world.



Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.



Leave a Reply

Your email address will not be published. Required fields are marked *