Выходные параметры хранимых процедур

Хранимая процедура может использовать не только входные параметры, через которые передаются значения в процедуру, но и выходные параметры. Выходные параметры по сути представляют результат работы процедуры, те значения, которые она возвращает пользователю.

Опять же возьмем наш базу данных и определим в ней следующую хранимую процедуру:

CREATE PROCEDURE [dbo].[sp_GetAgeRange]
    @name nvarchar(50),
	@minAge int out,
	@maxAge int out
AS
    SELECT @minAge = MIN(Age), @maxAge = MAX(Age) FROM Users WHERE Name LIKE '%' + @name + '%';

В этой процедуре определено три параметра, с помощью которых мы будем получать минимальный и максимальный возраст в базе данных. Через параметр @name мы будем получать имя пользователя, для которого осуществляется поиск минимального и максимального возраста. Причем в данном случае параметр по умолчанию имеет значение '%'. Данное значение указывает на произвольную строку. То есть если значение для этого параметра передано, то процедура будет находить возраст только для пользователей с этим именем. Если же значение для параметра не передано, то процедура будет находить минимальный и максимальный возраст всех пользователей вне зависимости от имени.

Параметры @minAge и @maxAge являются выходными благодаря указанию ключевого слова out в их определении. Через них мы собственно и будем получать минимальный и максимальный возраст.

Общий код добавления:

using System;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace HelloApp
{
    class Program
    {
        static async Task Main(string[] args)
        {
            string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;";

            string proc = @"CREATE PROCEDURE [dbo].[sp_GetAgeRange]
                                @name nvarchar(50),
	                            @minAge int out,
	                            @maxAge int out
                            AS
                                SELECT @minAge = MIN(Age), @maxAge = MAX(Age) FROM Users WHERE Name LIKE '%' + @name + '%';";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                SqlCommand command = new SqlCommand(proc, connection);

                await command.ExecuteNonQueryAsync();
            }
            Console.Read();
        }
    }
}

Теперь применим добавленную процедуру для получения диапазона возрастов по имени:

using System;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace HelloApp
{
    class Program
    {
        static string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;";

        static async Task Main(string[] args)
        {
            await GetAllUsersAsync();   // сначала выводим всех пользователей

            Console.Write("Введите имя пользователя:");
            string name = Console.ReadLine();

            await GetAgeRangeAsync(name);

            Console.Read();
        }

        private static async Task GetAllUsersAsync()
        {
            string sqlExpression = "SELECT * FROM Users";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                SqlCommand command = new SqlCommand(sqlExpression, connection);
                using (SqlDataReader reader = await command.ExecuteReaderAsync())
                {
                    if (reader.HasRows) // если есть данные
                    {
                        // выводим названия столбцов
                        Console.WriteLine($"{reader.GetName(0)}\t{reader.GetName(2)}\t{reader.GetName(1)}");

                        while (await reader.ReadAsync()) // построчно считываем данные
                        {
                            object id = reader.GetValue(0);
                            object name = reader.GetValue(2);
                            object age = reader.GetValue(1);
                            Console.WriteLine($"{id} \t{name} \t{age}");
                        }
                    }
                }
            }
        }
        private static async Task GetAgeRangeAsync(string name)
        {
            string sqlExpression = "sp_GetAgeRange";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                SqlCommand command = new SqlCommand(sqlExpression, connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter nameParam = new SqlParameter
                {
                    ParameterName = "@name",
                    Value = name
                };
                command.Parameters.Add(nameParam);

                // определяем первый выходной параметр
                SqlParameter minAgeParam = new SqlParameter
                {
                    ParameterName = "@minAge",
                    SqlDbType = SqlDbType.Int, // тип параметра

                    // указываем, что параметр будет выходным
                    Direction = ParameterDirection.Output
                };
                
                command.Parameters.Add(minAgeParam);

                // определяем второй выходной параметр
                SqlParameter maxAgeParam = new SqlParameter
                {
                    ParameterName = "@maxAge",
                    SqlDbType = SqlDbType.Int,
                    // указываем, что параметр будет выходным
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(maxAgeParam);

                await command.ExecuteNonQueryAsync();
                object minAge = command.Parameters["@minAge"].Value;
                object maxAge = command.Parameters["@maxAge"].Value;
                Console.WriteLine($"Минимальный возраст: {minAge}");
                Console.WriteLine($"Максимальный возраст: {maxAge}");
            }
        }
    }
}

Здесь вначале выводим список пользователей из базы данных с помощью метода GetAllUsersAsync, а затем с помощью вызова GetAgeRangeAsync ищем диапазон возрастов по имени пользователя.

При обращении к хранимой процедуре в методе GetAgeRangeAsync определяем входной параметр nameParam и два выходных параметры - minAgeParam и maxAgeParam. Выходные параметры команды определяются также, как и входные за тем исключением, что для свойства Direction нам надо установить значение ParameterDirection.Output.

SqlParameter minAgeParam = new SqlParameter
{
	ParameterName = "@minAge",
	SqlDbType = SqlDbType.Int, // тип параметра

	// указываем, что параметр будет выходным
	Direction = ParameterDirection.Output
};

По умолчанию все параметры имеют значение ParameterDirection.Input, которое указывает, что они являются входными.

После выполнения команды с помощью метода ExecuteNonOueryAsync() мы можем получить параметры по названию или по их индексу в коллекции параметров: command.Parameters["название параметра"]. И чтобы получить само значение параметра, надо использовать его свойство Value.

Теперь мы можем ввести имя пользователя, либо просто нажать на Enter, и процедура вернет нам выходные параметры. При этом может возникнуть ситуация, что пользователя с введенным именем не окажется в системе, и тогда параметры будут содержать пустые значения.

Пример работы программы:

Id		Name	Age
2		Alice	32
3		Bob		28
19		Tom		36
20		Bob		41
21		Tom		24

Введите имя пользователя: Bob
Минимальный возраст: 28
Максимальный возраст: 41