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
BEGINselect * from Employee
END
Once executed above stored procedure will be listed under the Stored procedure section.
Step3 – Execute SQL Stored Procedure
Naming Syntax
EXEC <Stored Procedure Name> <@Input>
Example
Exec GetEmployees
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’
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’
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.