You sometimes will be asked if you should create a stored procedure to perform a specific action. Stored Procedures can be used to do any CRUD operation. CRUD represents an acronym for the database operations Create, Read, Update, and Delete. There is no certain limitation to what you do with a Stored Procedures, only specific scenarios that make sense in your unique environment.
- To do a series of DML (Data Manipulation Language) operations.
- Suitable for a maintenance standpoint type of applications since troubleshooting might be easier if the logic is in one place.
- Stored Procedures can reduce SQL Injection attacks.
- A Stored Procedure separates the data layer from the application layer. When you implement an application a database designer/developer can manage the database objects while other traditional developers can focus on the program logic or application appearance.
- It helps security, by managing access for tables and other database objects based on user role.
- SQL Server has built-in functions to do most everything you need, so you might not have to write as much code
- If you are good at Transact-SQL, creating Stored Procedures for an application is fast and simple.
So you want to see an example? The simple Transact-SQL code would be like the example below, which will return all rows from this imaginary table.
SELECT * FROM MyDatabase.CustomerAddress
To create a stored procedure to do this the code would look like this:
CREATE PROCEDURE GetCustomerAddress AS SELECT * FROM MyDatabase.CustomerAddress GO
To call the procedure and return the contents from the table specified, the code would be:
EXEC GetCustomerAddress --or just simply GetCustomerAddress
You can read more about SQL Server stored procedures here.