1. Oh CRUD!
Let's talk about CRUD, which stands for Create, Read, Update and Delete.
2. Why stored procedures for CRUD?
Why should we use stored procedures to Create, Read, Update, and Delete data? Applications can use N-tier architecture, which utilizes various logical layers separating presentation functionality, business logic, data access functionality, and the data source. Stored procedures exist in the data source, and the data access layer only needs to execute them, instead of executing more complex SQL queries. This decouples the SQL code from the data access layer.
3. Why stored procedures for CRUD?
Stored procedures can also be used to improve security and prevent things like SQL injection attacks. When stored procedures are used properly, instead of executing dynamic SQL, they can help keep the database secure.
4. Why stored procedures for CRUD?
The SQL Server query optimizer creates an execution plan when a stored procedure is first executed. The plan is an attempt to calculate the most efficient way to implement the query submitted. The database engine follows the execution plan that exists. Since it takes a lot of resources for the query optimizer to create the execution plan, they are cached for future use. This reuse of the execution plan allows stored procedures to run faster compared to ad hoc SQL queries which need to be analyzed by the query optimizer each time they are executed.
5. C for CREATE
The code shown will create a stored procedure that will be used to create records in the TripSummary table. You should avoid the sp prefix when creating your own stored procedures, as it can identify a system stored procedure. An alternative is the cusp prefix, but you should be consistent and follow your organization's naming convention. Here we include the table name followed by the action to be taken on the table in the stored procedure name.
The TripSummary table only has two columns so we need two input parameters which will be used as the VALUES in the INSERT statement. We also select the record that was just inserted, which will be returned when the stored procedure is executed.
6. R for READ
The stored procedure created here will accept a TripDate input parameter and return the TripSummary records with a matching date value. Notice the consistency in naming convention. By including the table name in the stored procedure name, all stored procedures associated with the TripSummary table will be grouped together. Including the CRUD suffix makes it clear which stored procedure should be used for each database action.
7. U for UPDATE
This stored procedure will be used to update existing records in the TripSummary table. The input parameters correspond to the column values that could be changed, TripDate and TripHours. It should also include the column values used to identify the records to be changed. Then we use the UPDATE statement and SET the Date and TripHours columns to the input parameter values for the record with the matching TripDate value.
8. D for DELETE
We can't have CRUD without Delete! This stored procedure will accept a TripDate input parameter and delete the matching record from the TripSummary table. It will also return the affected row count as an output parameter by using the @@ROWCOUNT system variable. This keyword is helpful for understanding the impact of your stored procedures and should begin with the two at signs. Here @@ROWCOUNT captures the number of rows affected by the previous statement and assigns it to the @RowCountOut output parameter.
The @@ROWCOUNT system variable does get reset and you can't control when so you should store the value in a local variable to evaluate.
9. Your turn for CRUD!
Now it's your turn to create CRUD!