Mastering Named, Positional, and Input/Output Parameters in Stored Procedures with EF Core

When working with SQL-stored procedures via Entity Framework Core (EF Core), you may encounter concepts such as named parameters, positional parameters, input/output parameters, and output parameters. Each of these has specific use cases and advantages in different scenarios. This article explores these concepts and provides practical examples of how to use them in EF Core.

Create a Sample SQL Table

We will create a sample table called Employees to use as the basis for this example.

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50) NOT NULL,
    Salary DECIMAL(18, 2) NOT NULL
);

-- Insert some sample data
INSERT INTO Employees (Name, Salary) VALUES
('Alice', 50000.00),
('Bob', 45000.00),
('Charlie', 60000.00);

Positional Parameters

Positional parameters are used to map values to stored procedure parameters in the order they appear within the procedure. While this approach is simple for procedures with a small number of parameters, it can make your code less readable if there are many parameters or when their meaning is unclear.

Example of Positional Parameters

Given the following stored procedure in SQL Server.

CREATE PROCEDURE GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SELECT Name, Salary
    FROM Employees
    WHERE Id = @EmployeeId;
END

In EF Core, you can pass the input parameter based on its position.

using var context = new ApplicationDbContext(options);

// Define the positional parameter value
int employeeId = 1;

// Execute the stored procedure with positional parameters
var employeeDetails = context.Database.ExecuteSqlRaw(
    "EXEC GetEmployeeById @p0",
    employeeId);

// Or
// var employeeDetails = context.Database.ExecuteSqlRaw(
//    "EXEC GetEmployeeById {0}",
//    employeeId);

Here, @p0 represents the first position in the parameter list.

or {0} corresponds to the first position in the parameter list.

Named Parameters

Named parameters specify the name of the parameter explicitly when passing values to a stored procedure. This approach makes the code more readable and avoids confusion, especially when multiple parameters are involved.

Example of Named Parameters

Using the same stored procedure defined above.

using var context = new ApplicationDbContext(options);

// Define the named parameter value
int employeeId = 1;

// Execute the stored procedure with named parameters
var employeeDetails = context.Database.ExecuteSqlRaw(
    "EXEC GetEmployeeById @EmployeeId = {0}",
    employeeId
);

// or
// var employeeDetails = context.Database.ExecuteSqlRaw(
//    "EXEC GetEmployeeById @EmployeeId",
//    new SqlParameter("@EmployeeId", employeeId)
//    );

Here, @EmployeeId explicitly identifies the parameter being passed, increasing clarity and maintainability.

Input/Output Parameters

Input/Output (also known as INOUT) parameters allow data to be sent to a stored procedure and retrieved after being modified within the procedure. These are particularly useful when you need to pass initial values and retrieve updated values.

Example of SQL Procedure with Input/Output Parameter

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeId INT,               -- Input Parameter
    @InputOutputSalary DECIMAL(18,2) OUTPUT -- Input/Output Parameter
AS
BEGIN
    -- Check if the provided input salary matches the current salary
    IF EXISTS (SELECT Salary FROM Employees WHERE Id = @EmployeeId AND Salary = @InputOutputSalary)
    BEGIN
        -- Increase the salary by 10% if it matches
        UPDATE Employees
        SET Salary = Salary * 1.10
        WHERE Id = @EmployeeId;

        -- Update the Output parameter with the new salary
        SELECT @InputOutputSalary = Salary
        FROM Employees
        WHERE Id = @EmployeeId;
    END
    ELSE
    BEGIN
        -- Return the current salary if the input does not match
        SELECT @InputOutputSalary = Salary
        FROM Employees
        WHERE Id = @EmployeeId;
    END
END

C# Implementation with EF Core

To pass and retrieve Input/Output parameters.

using var context = new ApplicationDbContext(options);

// Define the input/output parameter
int employeeId = 1;
var updatedSalary = new SqlParameter("@InputOutputSalary", SqlDbType.Decimal)
{
    Direction = ParameterDirection.InputOutput,
    Value = 5000.00m // Example: Initial salary value being passed to the procedure
};

// Execute the stored procedure
context.Database.ExecuteSqlRaw(
    "EXEC UpdateEmployeeSalary @EmployeeId = {0}, @InputOutputSalary OUTPUT",
    employeeId, updatedSalary
);

// Retrieve the updated salary value
decimal finalSalary = Convert.ToDecimal(updatedSalary.Value);

Console.WriteLine($"Updated Salary: {finalSalary}");

Output Parameters

Output (OUT) parameters are used when you need to retrieve data from the stored procedure that was calculated or determined during its execution. Unlike Input/Output parameters, output parameters do not accept values before execution.

Example of SQL Procedure with Output Parameters

CREATE PROCEDURE GetEmployeeNameAndSalary
    @EmployeeId INT,
    @EmployeeName NVARCHAR(50) OUTPUT,
    @EmployeeSalary DECIMAL(18, 2) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = Name, @EmployeeSalary = Salary
    FROM Employees
    WHERE Id = @EmployeeId;
END

C# Implementation with EF Core

To pass and retrieve Output parameters.

using var context = new ApplicationDbContext(options);

// Define the output parameters
int employeeId = 1;

var employeeName = new SqlParameter("@EmployeeName", SqlDbType.NVarChar, 50)
{
    Direction = ParameterDirection.Output
};
var employeeSalary = new SqlParameter("@EmployeeSalary", SqlDbType.Decimal)
{
    Direction = ParameterDirection.Output
};

// Execute the stored procedure
context.Database.ExecuteSqlRaw(
    "EXEC GetEmployeeNameAndSalary @EmployeeId = {0}, @EmployeeName OUTPUT, @EmployeeSalary OUTPUT",
    employeeId, employeeName, employeeSalary
);

// Retrieve the output values
string name = employeeName.Value.ToString();
decimal salary = Convert.ToDecimal(employeeSalary.Value);

Console.WriteLine($"Employee Name: {name}, Salary: {salary}");

Key Differences Between Input/Output and Output Parameters

Type	Purpose	Initial Value	Modified Value After Execution
Input Parameters	Send data to the procedure	Must be specified	Unchanged
Output Parameters	Retrieve data from the procedure	No initial value	Set by the procedure
Input/Output Parameters	Send data to the procedure and retrieve modified data	Must be specified	Modified by the procedure

Best Practices

Use Named Parameters for Readability: Named parameters improve clarity, especially when the procedure has many parameters.
Parameter Data Type Mapping: Ensure the SQL types match the C# types (e.g., NVARCHAR as string, INT as int, DECIMAL as decimal) for seamless execution.
Avoid Hardcoding SQL: If possible, use EF Core's parameters to avoid SQL injection risks.
Asynchronous Execution: For non-blocking operations, use ExecuteSqlRawAsync() in EF Core to run procedures asynchronously.
Handle Null Values: Ensure you handle null values appropriately in both SQL and C#. For example, DBNull.Value can be used to pass null values to SQL parameters.

Conclusion

Named parameters and positional parameters allow you to pass input values in different ways, while output and input/output parameters provide mechanisms for retrieving or modifying data. By understanding these concepts and leveraging EF Core's raw SQL execution methods, you can integrate stored procedures effectively in your C# applications. Always prioritize readability, maintainability, and security when working with database logic.