SQL Stored Procedures

Stored Procedures: Types and Creation Tutorial for Beginners

1. Introduction to Stored Procedures: Stored procedures are precompiled and stored sets of SQL statements that can be executed with a single command. They offer several advantages, such as improved performance, code reusability, and enhanced security. This tutorial will guide you through the different types of stored procedures and provide step-by-step instructions on creating stored procedures in SQL Server.

2. Types of Stored Procedures: SQL Server supports three types of stored procedures:

2.1. Transact-SQL Stored Procedures: Transact-SQL (T-SQL) stored procedures are the most common type. They are written in the T-SQL language and can include complex logic, control flow statements, and SQL queries. T-SQL stored procedures are executed within the SQL Server database engine.

2.2. CLR Stored Procedures: CLR stored procedures are written in .NET languages, such as C# or Visual Basic, and run within the Common Language Runtime (CLR) of SQL Server. CLR stored procedures can leverage the full power of .NET and are useful for complex calculations or integrating with external systems.

2.3. Extended Stored Procedures: Extended stored procedures are written in programming languages like C or C++ and are typically used for low-level system tasks. They provide direct access to the SQL Server process and require advanced programming skills. Extended stored procedures are less common in modern SQL Server development.

3. Creating a Stored Procedure: To create a stored procedure in SQL Server, follow these steps:

3.1. Defining the Stored Procedure: Begin by defining the stored procedure's name, input parameters, and output parameters (if any). Use the CREATE PROCEDURE statement to initiate the creation process.

Example:

sql
CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    -- Procedure logic goes here
END

3.2. Writing the Procedure Logic: Within the stored procedure, write the necessary T-SQL statements to perform the desired operations. This can include selecting data, inserting records, updating values, or executing other stored procedures.

Example:

sql
CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END

3.3. Executing the Stored Procedure: Once the stored procedure is defined and its logic is written, it can be executed using the EXEC or EXECUTE statement.

Example:

sql
EXEC GetEmployeeByID @EmployeeID = 123;

4. Modifying and Dropping Stored Procedures: To modify an existing stored procedure, you can use the ALTER PROCEDURE statement to update its definition. If you no longer need a stored procedure, you can remove it from the database using the DROP PROCEDURE statement.

Example:

sql
ALTER PROCEDURE GetEmployeeByID
    @EmployeeID INT,
    @IncludeAddress BIT = 0 -- Added a new optional parameter
AS
BEGIN
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        CASE WHEN @IncludeAddress = 1 THEN Address END AS Address
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END

DROP PROCEDURE GetEmployeeByID;

Creating Transact-SQL Stored Procedures

Introduction A Transact-SQL (T-SQL) stored procedure is a group of T-SQL statements that can be executed as a single unit. It allows you to encapsulate complex logic and reusable code, providing a more modular approach to database development.

Syntax The basic syntax for creating a T-SQL stored procedure is as follows:

sql
CREATE PROCEDURE procedure_name
[ { @parameter1 data_type [ = default_value ] } [ ,...n ] ]
[ WITH { RECOMPILE | ENCRYPTION | EXECUTE AS { CALLER | SELF | OWNER | 'user_name' } } ]
AS
BEGIN
    -- T-SQL statements
END

Example Let's create a simple T-SQL stored procedure that retrieves all employees from a table:
```
sql
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END
```
Executing the Stored Procedure Once the stored procedure is created, you can execute it using the following code:
```
sql
EXEC GetEmployees;
```
The EXEC keyword is used to execute the stored procedure.
Parameters Stored procedures can accept input parameters to provide dynamic behavior. Here's an example of a stored procedure with parameters:
```
sql
CREATE PROCEDURE GetEmployeesByDepartment
    @departmentId INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentId = @departmentId;
END
```
You can execute this stored procedure by passing the @departmentId parameter:
```
sql
EXEC GetEmployeesByDepartment @departmentId = 1;
```
The @departmentId parameter is specified with the @ symbol.

Creating CLR Stored Procedures

Introduction Common Language Runtime (CLR) stored procedures allow you to write stored procedures using .NET languages like C# or Visual Basic. CLR stored procedures provide additional flexibility and functionality compared to T-SQL stored procedures.
Steps To create a CLR stored procedure, follow these steps:
a. Enable CLR Integration Before creating CLR objects, CLR integration must be enabled on the SQL Server instance. Execute the following code to enable it:
```
sql
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
```
b. Create the CLR Stored Procedure Here's an example of creating a CLR stored procedure in C#:
```
csharp
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class MyClrStoredProcedure
{
    [SqlProcedure]
    public static void HelloWorld()
    {
        SqlContext.Pipe.Send("Hello, World!");
    }
}
```
c. Deploy the Assembly Once the CLR stored procedure is implemented, you need to deploy it to SQL Server using the following code:
```
sql
CREATE ASSEMBLY MyClrStoredProcedure
FROM 'C:\Path\To\MyClrStoredProcedure.dll'
WITH PERMISSION_SET = SAFE;
```
d. Create the CLR Stored Procedure Finally, create the CLR stored procedure by referencing the deployed assembly:
```
sql
CREATE PROCEDURE MyClrStoredProcedure
AS EXTERNAL NAME MyClrStoredProcedure.[Namespace.ClassName].HelloWorld;
```
e. Execute the CLR Stored Procedure To execute the CLR stored procedure, use the same EXEC keyword as for T-SQL stored procedures:
```
sql
EXEC MyClrStoredProcedure;
```

Creating Extended Stored Procedures

Introduction Extended stored procedures are written in C or C++ and provide additional functionality beyond what is available with T-SQL or CLR stored procedures. They allow you to call external code libraries or system functions from within SQL Server.
Steps To create an extended stored procedure, follow these steps:
a. Write the C/C++ Code First, write the code for the extended stored procedure using C or C++. This code typically interacts with the operating system or external libraries.
b. Build the DLL Compile the C/C++ code into a dynamic-link library (DLL). Ensure that the DLL is compatible with the version of SQL Server you're using.
c. Register the DLL Register the DLL in SQL Server using the sp_addextendedproc system stored procedure:
```
sql
EXEC sp_addextendedproc 'MyExtendedStoredProcedure', 'C:\Path\To\MyExtendedStoredProcedure.dll';
```
d. Create the Extended Stored Procedure Once registered, create the extended stored procedure using the following code:
```
sql
CREATE PROCEDURE MyExtendedStoredProcedure
AS EXTERNAL NAME MyExtendedStoredProcedure.[Namespace.ClassName].MethodName;
```
e. Execute the Extended Stored Procedure Execute the extended stored procedure using the EXEC keyword:
```
sql
EXEC MyExtendedStoredProcedure;
```
The external code will be executed, providing the additional functionality defined in the C/C++ code.

Remember to adjust the file paths, assembly names, namespaces, class names, and method names according to your specific implementation.

5. Conclusion: Stored procedures are powerful tools in SQL Server that enable you to encapsulate complex logic and improve the performance and security of your database operations. By following this tutorial, you should now have a solid understanding of the different types of stored procedures and how to create them in SQL Server.