Хотите передать список ID пользователей, чтобы возвращать список имен. У меня есть план, чтобы справиться с выводимой имена (с сливаются или что-то другое), но пытаюсь найти лучший способ, чтобы перейти в список идентификаторов пользователей. Кишки мой sproc будет выглядеть примерно так:
create procedure [dbo].[get_user_names]
@user_id_list, --which would equal a list of incoming ID numbers like (5,44,72,81,126)
@username varchar (30) output
as
select last_name+', '+first_name
from user_mstr
where user_id in @user_id_list
Передав значения для @user_id_list это моя главная забота здесь.
Предпочтительный способ для передачи массива значений в хранимую процедуру в SQL Server является использование возвращающая табличное значение параметров.
Сначала вы определите тип такой:
CREATE TYPE UserList AS TABLE ( UserID INT );
Затем вы используете этот тип в хранимой процедуре:
create procedure [dbo].[get_user_names]
@user_id_list UserList READONLY,
@username varchar (30) output
as
select last_name+', '+first_name
from user_mstr
where user_id in (SELECT UserID FROM @user_id_list)
Поэтому прежде чем вызвать эту хранимую процедуру, вы заполняете табличную переменную:
DECLARE @UL UserList;
INSERT @UL VALUES (5),(44),(72),(81),(126)
И, наконец, вызвать СП:
EXEC dbo.get_user_names @UL, @username OUTPUT;
Насколько я могу судить, есть три основных претендента: табличное значение параметров, запятыми список, и строку JSON.
Начиная с 2016 года, вы можете использовать встроенный STRING_SPLIT если вы хотите разделителями маршруту: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql
Это может быть самый простой/самый простой/простой подход.
Также начиная с 2016 года, JSON может быть передан как аргумент и используется с OPENJSON: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql
Что's наверное лучше, если у вас есть более структурированный набор для передачи данных может быть существенно переменной в ее схеме.
Возвращающие табличное значение параметры, кажется, используется для канонического способа передачи более структурированных параметров, и они по-прежнему хорошо, если вам нужно, что структуры, эксплицитности, и основные значения/тип проверки. Они могут быть немного громоздким на стороне потребителя, хотя. Если вы Don'т иметь, 2016+, это, наверное, по умолчанию/лучший вариант.
Я думаю, что это'ы компромисс между любой из этих конкретных аспектов, а также ваших предпочтений для явной о структуре параметров, это означает, даже если вы имеете 2016+ можно явно указать тип/схемы параметра, а не передать строку и разобрать его как-то.
Я решил эту проблему следующим образом:
строка имя пользователя=" по себе";
например: в C#
имяпользователя= "и 5,44,72,81,126 и";
и отправить к SQL-серверу
объект sqlparameter парам = ЦМД.Параметры.AddWithValue (на" user_id_list на"@;, имяпользователя);
создавать функции dbo.SplitInts ( @список тип varchar(Макс), @разделитель тип varchar(255) ) стол возвращается как возврат ( выберите пункт = преобразовать(ИНТ пункт) от ( выберите пункт = х.я.значение('(./текст())[1]', 'тип varchar(Макс)') от ( выберите [формат XML] = преобразовать(в XML, '<и>'
- заменить(@список, @разделители, '</а><и>') + '</а>').запрос('.') ) как применять [в XML].узлы('Я') как X(I)), а г если элемент не равен null );
выберите ид_пользователя = элемент от dbo.SplitInts(@user_id_list, ',');
Базы данных Azure, данных Azure Втч и из SQL Server 2016 вы можете использовать STRING_SPLIT, чтобы достичь аналогичного результата, что описал @Воробья.
Обработка кода от @воробей
WHERE user_id IN (SELECT value FROM STRING_SPLIT( @user_id_list, ',')
Простой и эффективный способ приема список значений в хранимую процедуру
Вы можете попробовать это:
create procedure [dbo].[get_user_names]
@user_id_list varchar(2000), -- You can use any max length
@username varchar (30) output
as
select last_name+', '+first_name
from user_mstr
where user_id in (Select ID from dbo.SplitString( @user_id_list, ',') )
А вот пользовательские функции для SplitString:
Create FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
Вы можете использовать этот простой 'встроенный' метод построения параметра string_list_type (работает в SQL Server 2014 г.):
declare @p1 dbo.string_list_type
insert into @p1 values(N'myFirstString')
insert into @p1 values(N'mySecondString')
Пример использования при выполнении хранимой процедуре:
exec MyStoredProc @MyParam=@p1