A Basic Introduction To Stored Procedures

SQL Server stored procedures are used to group Transact-SQL statements into logical units that can be reused. When working on the SQL Server Database, the stored procedure is stored as a named object.

Benefits of Stored Procedures

There are lots of benefits to using stored procedures in your programs. Between them are:

  • Stored procedures in SQL Server can accept several input parameters and return multiple values as output parameters.
  • A stored procedure provides an important and reliable layer of security between the user interface and the database. It gives security by controlling the data access because end users can enter or change data, but can’t write procedures. It serves to preserve data integrity because the information is being entered consistently.
  • The usage of stored procedures can dramatically reduce network traffic between the client and server because the commands are executed as a single batch (or group) of code. This means that the only thing being sent to the network is the call to execute the procedure, instead of costly sending every single line of code individually.
  • Plus, it can help with overall performance. Since when you call a stored procedure for the first time, it creates an execution plan and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan to execute the stored procedure at a very fast and reliable performance.

Basic of Stored Procedures

Creating a simple stored procedure

We will use an example of a stored procedure that returns a select statement, you use the CREATE PROCEDURE statement as follows:

CREATE PROCEDURE sp_name
AS
BEGIN
SELECT
column1, column2
FROM
table1
ORDER BY
column1;
END;

Understanding the above syntax:

  • The usp_PersonData is the name of our stored procedure.
  • The AS keyword separates the heading and the body of the stored procedure.
  • The BEGIN and END keywords surrounding the statement are surround the body of our store procedure, and also help to make the code clear.

A quick note that instead of theCREATE PROCEDURE keywords, you can use CREATE PROC to make the statement shorter.

If you are in SSMS (SQL Server Management Studio) you can find the stored procedure in the Object Explorer. Right under Programmability > Stored Procedures. Sometimes, you need to click the Refresh button to update the database objects.

To execute a stored procedure, you use the EXECUTE or EXEC statement followed by the name of the stored procedure:

EXECUTE sp_name;
EXEC sp_name;

To modify an existing stored procedure, you use the ALTER PROCEDURE statement.

ALTER PROCEDURE sp_name
AS
BEGIN
SELECT
column1, column2
FROM
table1
ORDER BY
column2;
END;

To delete a stored procedure, you use the DROP PROCEDURE or DROP PROC statement:

DROP PROCEDURE sp_name;
DROP PROC sp_name;

Parameters in Stored Procedures

Another last thing o know is that you can pass a parameter to a stored procedure. A feature that allows the code to be more versatile and reusable.

CREATE PROCEDURE sp_name
(@parameter1 varchar(50))
AS
BEGIN
SELECT
column1, column2
FROM
table1
WHERE
column1 = @parameter1;
END;

In this case, we are passing @pafameter1 to the stored procedure and using it to filter our select statement. Note that to execute our stored procedure we now should write: (we are passing ‘Example’ as our parameter)

EXEC sp_name 'Example'

More things are needed to master stored procedures and use them in an efficient way. This was a brief introduction and hopefully encouraged you to investigate further.

Hope that you have first understood why Sored Procedures are useful, and then the basics of working with them. You have learned how to manage SQL Server stored procedures including creating, executing, modifying, and deleting them.

Thanks for reading, and have a great rest of your day! Keep coding.


A Basic Introduction To Stored Procedures was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.

SQL Server stored procedures are used to group Transact-SQL statements into logical units that can be reused. When working on the SQL Server Database, the stored procedure is stored as a named object.

Benefits of Stored Procedures

There are lots of benefits to using stored procedures in your programs. Between them are:

  • Stored procedures in SQL Server can accept several input parameters and return multiple values as output parameters.
  • A stored procedure provides an important and reliable layer of security between the user interface and the database. It gives security by controlling the data access because end users can enter or change data, but can’t write procedures. It serves to preserve data integrity because the information is being entered consistently.
  • The usage of stored procedures can dramatically reduce network traffic between the client and server because the commands are executed as a single batch (or group) of code. This means that the only thing being sent to the network is the call to execute the procedure, instead of costly sending every single line of code individually.
  • Plus, it can help with overall performance. Since when you call a stored procedure for the first time, it creates an execution plan and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan to execute the stored procedure at a very fast and reliable performance.

Basic of Stored Procedures

Creating a simple stored procedure

We will use an example of a stored procedure that returns a select statement, you use the CREATE PROCEDURE statement as follows:

CREATE PROCEDURE sp_name
AS
BEGIN
SELECT
column1, column2
FROM
table1
ORDER BY
column1;
END;

Understanding the above syntax:

  • The usp_PersonData is the name of our stored procedure.
  • The AS keyword separates the heading and the body of the stored procedure.
  • The BEGIN and END keywords surrounding the statement are surround the body of our store procedure, and also help to make the code clear.

A quick note that instead of theCREATE PROCEDURE keywords, you can use CREATE PROC to make the statement shorter.

If you are in SSMS (SQL Server Management Studio) you can find the stored procedure in the Object Explorer. Right under Programmability > Stored Procedures. Sometimes, you need to click the Refresh button to update the database objects.

To execute a stored procedure, you use the EXECUTE or EXEC statement followed by the name of the stored procedure:

EXECUTE sp_name;
EXEC sp_name;

To modify an existing stored procedure, you use the ALTER PROCEDURE statement.

ALTER PROCEDURE sp_name
AS
BEGIN
SELECT
column1, column2
FROM
table1
ORDER BY
column2;
END;

To delete a stored procedure, you use the DROP PROCEDURE or DROP PROC statement:

DROP PROCEDURE sp_name;
DROP PROC sp_name;

Parameters in Stored Procedures

Another last thing o know is that you can pass a parameter to a stored procedure. A feature that allows the code to be more versatile and reusable.

CREATE PROCEDURE sp_name
(@parameter1 varchar(50))
AS
BEGIN
SELECT
column1, column2
FROM
table1
WHERE
column1 = @parameter1;
END;

In this case, we are passing @pafameter1 to the stored procedure and using it to filter our select statement. Note that to execute our stored procedure we now should write: (we are passing ‘Example’ as our parameter)

EXEC sp_name 'Example'

More things are needed to master stored procedures and use them in an efficient way. This was a brief introduction and hopefully encouraged you to investigate further.

Hope that you have first understood why Sored Procedures are useful, and then the basics of working with them. You have learned how to manage SQL Server stored procedures including creating, executing, modifying, and deleting them.

Thanks for reading, and have a great rest of your day! Keep coding.


A Basic Introduction To Stored Procedures was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.


Print Share Comment Cite Upload Translate
APA
Emilio Eguia | Sciencx (2024-03-28T08:19:27+00:00) » A Basic Introduction To Stored Procedures. Retrieved from https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/.
MLA
" » A Basic Introduction To Stored Procedures." Emilio Eguia | Sciencx - Tuesday November 29, 2022, https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/
HARVARD
Emilio Eguia | Sciencx Tuesday November 29, 2022 » A Basic Introduction To Stored Procedures., viewed 2024-03-28T08:19:27+00:00,<https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/>
VANCOUVER
Emilio Eguia | Sciencx - » A Basic Introduction To Stored Procedures. [Internet]. [Accessed 2024-03-28T08:19:27+00:00]. Available from: https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/
CHICAGO
" » A Basic Introduction To Stored Procedures." Emilio Eguia | Sciencx - Accessed 2024-03-28T08:19:27+00:00. https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/
IEEE
" » A Basic Introduction To Stored Procedures." Emilio Eguia | Sciencx [Online]. Available: https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/. [Accessed: 2024-03-28T08:19:27+00:00]
rf:citation
» A Basic Introduction To Stored Procedures | Emilio Eguia | Sciencx | https://www.scien.cx/2022/11/29/a-basic-introduction-to-stored-procedures/ | 2024-03-28T08:19:27+00:00
https://github.com/addpipe/simple-recorderjs-demo