SQL Server Stored Procedures

A stored procedure is a SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute

EXEC procedure_name;

Example

CREATE PROCEDURE sp_students
AS
BEGIN
SELECT * FROM students
BEGIN
GO;

Example

EXEC students;

Stored Procedure With Parameters

CREATE PROCEDURE sp_students @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Students WHERE City = @City AND PostalCode = @PostalCode
GO;

Example

EXEC sp_students @City = 'raipur', @PostalCode = '492001';

SQL Server stored procedures offer several benefits, including:

1. Performance: Stored procedures are pre-compiled and optimized by the database engine, which means they can be executed more quickly than dynamic SQL statements. This can lead to significant performance improvements, especially in applications that require frequent and complex data access.
2. Security: Stored procedures can be used to implement fine-grained security controls by granting execute permissions to specific users or roles. This allows you to limit access to sensitive data and ensure that only authorized users can execute specific data access operations.
3. Reusability: Stored procedures can be reused across multiple applications and components, reducing the need to duplicate code and making it easier to maintain a consistent data access strategy.
4. Abstraction: Stored procedures provide a layer of abstraction between the application and the underlying data store, which can make it easier to change the underlying data schema or data access strategy without affecting the application.
5. Concurrency Control: Stored procedures can be used to implement transactional logic and concurrency control, which can help ensure data consistency and integrity in multi-user environments.
6. Code Organization: Stored procedures can help to organize the code, making it more readable and easier to maintain.
7. Portability: Stored procedures are database independent and can be ported to different RDBMS with minimal modifications.