I am creating sample tables that will be used in the examples in this article. Performance: The SQL Server stored procedure when executed for the first time creates a plan and stores it in the buffer pool so that the plan can be reused when it executes next time. Stored Procedures are compiled pieces of database engine specific SQL code that can run on the server - thus without the network traffic involved in pulling. Use third-party tools like ApexSQL Decrypt to decrypt the encrypted stored procedures. we can also encrypt the stored procedures while creating them so that source code inside the stored procedure is not visible. Security: Stored procedures reduce the threat by eliminating direct access to the tables. Reusable: Stored procedures can be executed by multiple users or multiple client applications without the need of writing the code again. Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the whole T-SQL code. so, when we want to change the logic inside the procedure we can just do it by simple ALTER PROCEDURE statement. SQL Server Stored procedures eliminate such challenges by storing the code in the database. For example, If the T-SQL queries are written in the application and if we need to change the logic, we must change the code in the application and re-deploy it. It can be easily modified: We can easily modify the code inside the stored procedure without the need to restart or deploying the application. To understand differences between functions and stored procedures in SQL Server, you can refer to this article, Functions vs stored procedures in SQL Server and to learn about Partial stored procedures in SQL Server, click Partial stored procedures in SQL Server. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure, returns the result set if any. SQL Server stored procedure is a batch of statements grouped as a logical unit and stored in the database. In this article, we will learn how to create stored procedures in SQL Server with different examples.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |