Lesson 1

You will find that much of your database programming involves reissuing the same commands over and over. This is because most businesses tend to adopt uniform ways of handling the many transactions that occur day to day.
Stored procedures can be used to automate repetitive tasks and to make them faster and easier. You can also use stored procedures to automate the process of enforcing business rules. Proper development and usage of stored procedures can save you time and allow you to focus your programming skills on more interesting programming tasks.

Introduction to Stored Procedures
After completing this module, you will be able to:

1. Describe and define stored procedures
2. Enforce business rules
3. Create, modify, and delete stored procedures
4. Use input and output parameters
5. Test your stored procedures

The top two use cases for using Stored Procedures in Microsoft SQL Server 2019 are as follows:

1. Performance Optimization: Stored Procedures are precompiled collections of SQL statements that are stored under a name and processed as a unit. They are compiled into a single execution plan when first executed, which is then cached by SQL Server. Subsequent executions of the stored procedure benefit from reduced parsing and compilation overhead, resulting in significantly faster execution times. This is particularly advantageous for complex queries and frequently executed operations. By encapsulating these operations in a stored procedure, SQL Server leverages the efficiency of precompiled code, which can lead to substantial performance gains, especially in high-transaction environments.
2. Enhanced Security and Access Control: Stored Procedures provide a robust mechanism for controlling access to the database at a granular level. By encapsulating the SQL logic within a stored procedure, the database administrator can restrict direct access to underlying tables, allowing users to interact with the data only through predefined procedures. This approach ensures that users execute only authorized operations, reducing the risk of accidental or malicious data modifications. Additionally, stored procedures can be assigned specific permissions, enabling administrators to precisely control who can execute, modify, or view the procedure's code. This level of control is essential in environments where data security and integrity are paramount.

In summary, the use of Stored Procedures in MS SQL Server 2019 is primarily beneficial for enhancing performance through precompiled execution plans and for strengthening database security and access management.

CREATE PROCEDURE [dbo].[usp_1]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE =
N'us_english'
)
SELECT c1, c2 from dbo.T1
END
GO
ALTER PROCEDURE [dbo].[usp_1]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE =
N'us_english'
)
SELECT c1 from dbo.T1
END
GO

You can now perform ALTER operations on natively compiled stored procedures using the ALTER PROCEDURE statement . Use sp_recompile to recompile stored procedures on the next execution
In the next lesson I will discuss what a stored procedure is and how it is used.