Вызов хранимой процедуры со всеми комбинациями значений, хранящимися в нескольких таблицах с одним столбцом
Вот таблицы, с которыми я работаю, и описание проблемы, которую я пытаюсь решить в T-SQL (SQL Server 2022):
-- Create single-column tables to hold values.
CREATE TABLE [dbo].[Attr_1_Values]
(
[Attr_1_Value] VARCHAR(30) NOT NULL PRIMARY KEY
);
CREATE TABLE [dbo].[Attr_2_Values]
(
[Attr_2_Value] VARCHAR(30) NOT NULL PRIMARY KEY
);
CREATE TABLE [dbo].[Attr_3_Values]
(
[Attr_3_Value] VARCHAR(30) NOT NULL PRIMARY KEY
);
-- Insert rows (individual values in this case) into the just-created tables.
INSERT INTO [dbo].[Attr_1_Values] ([Attr_1_Value]) VALUES ('Attr_1_val_1');
INSERT INTO [dbo].[Attr_1_Values] ([Attr_1_Value]) VALUES ('Attr_1_val_2');
INSERT INTO [dbo].[Attr_1_Values] ([Attr_1_Value]) VALUES ('Attr_1_val_3');
INSERT INTO [dbo].[Attr_1_Values] ([Attr_1_Value]) VALUES ('Attr_1_val_4');
INSERT INTO [dbo].[Attr_2_Values] ([Attr_2_Value]) VALUES ('Attr_2_val_1');
INSERT INTO [dbo].[Attr_2_Values] ([Attr_2_Value]) VALUES ('Attr_2_val_2');
INSERT INTO [dbo].[Attr_3_Values] ([Attr_3_Value]) VALUES ('Attr_3_val_1');
INSERT INTO [dbo].[Attr_3_Values] ([Attr_3_Value]) VALUES ('Attr_4_val_2');
-- Create a stored procedure that will take as parameters values from the tables above.
CREATE PROCEDURE [dbo].[My_Stored_Procedure]
@Attr_1_p AS VARCHAR(30),
@Attr_2_p AS VARCHAR(30),
@Attr_3_p AS VARCHAR(30)
AS
BEGIN
-- Stored procedure logic goes here.
END;
-- Generate all combinations of values from the [Attr_1_Values], [Attr_2_Values], and [Attr_3_Values] tables.
-- Note: From this point on, pseudocode and proper T-SQL code are mixed to best try to convey my intent.
Attr_Combinations = SELECT [Attr_1_Value], [Attr_2_Value], [Attr_3_Value] FROM
[dbo].[Attr_1_Values] CROSS JOIN
[dbo].[Attr_2_Values] CROSS JOIN
[dbo].[Attr_3_Values];
-- Somehow, invoke [dbo].[My_Stored_Procedure] with each individual combination.
-- In this example, that would be 4*2*2 = 16 invocations of [dbo].[My_Stored-Procedure].
LOOP OVER Attr_Combinations
BEGIN
EXEC [dbo].[My_Stored_Procedure]
@Attr_1_p = One_Attr_Combination.Attr_1_Value,
@Attr_2_p = One_Attr_Combination.Attr_2_Value,
@Attr_3_p = One_Attr_Combination.Attr_3_Value;
END;
Как я могу выполнить желаемые вызовы [dbo].[My_Stored_Procedure], чтобы каждый вызов передавал одну комбинацию значений, как описано выше?
Редактировать для ясности: подход на основе множеств против итерации
В моем реальном приложении [dbo].[My_Stored_Procedure] будет вызываться по мере необходимости с одним конкретным набором значений для @Attr_1_p, @Attr_2_p и @Attr_3_p. Именно в моей тестовой программе для этой хранимой процедуры я хочу, чтобы для обеспечения надежности тестирования вызывалась каждая возможная комбинация @Attr_1_p, @Attr_2_p и @Attr_3_p.
Учитывая, как хранимая процедура будет использоваться в реальном приложении, желательно, чтобы она имела текущую подпись. Я мог бы изменить его подпись, чтобы получить таблицу (а затем работать с этой таблицей на основе наборов), но это в некоторой степени усложнило бы клиентский код, а не просто передавало бы значения @Attr_1, @Attr_2 и @Attr_3. он уже имеет под рукой, ему придется сначала построить таблицу из одной строки из этих значений, прежде чем вызывать хранимую процедуру.
Я понимаю и ценю предложения использовать подход, основанный на множествах, и сам бы предпочел это, если бы не желание сохранить сигнатуру хранимой процедуры, чтобы вызов был как можно более естественным для клиентского кода. Поскольку подход Ицхака Хабинского позволяет мне это сделать и поскольку мне удалось успешно реализовать его подход, я принял его ответ. Спасибо всем, кто прокомментировал!
Дебаты, похоже, показывают, что курсор — лучший подход stackoverflow.com/questions/2077948/…
почему бы не переписать процедуру, чтобы она могла обрабатывать несколько комбинаций одновременно
Согласен, повторение одной и той же процедуры с помощью курсора будет очень медленным и сложным.
Для дальнейшего контекста это, скорее всего, будет означать изменение вашей процедуры dbo.My_Stored_Procedure, чтобы она имела параметр типа таблицы, а затем работу с данными в этой таблице на основе набора (циклический просмотр этих данных в процедуре просто решает проблему, это это не решает).
Ответы 1
Пожалуйста, проверьте следующий концептуальный пример.
Просто вставьте результат вашего SELECT с CROSS JOINs в табличную переменную и повторите логику.
DECLARE @tbl TABLE (...);
INSERT INTO @tbl (...)
SELECT [Attr_1_Value], [Attr_2_Value], [Attr_3_Value] FROM
[dbo].[Attr_1_Values] CROSS JOIN
[dbo].[Attr_2_Values] CROSS JOIN
[dbo].[Attr_3_Values];
SQL
DECLARE @tbl TABLE (ID INT PRIMARY KEY, [Name] VARCHAR(50));
INSERT @tbl (ID, [Name]) VALUES
(1, 'Employee'),
(2, 'Department'),
(3, 'Class');
DECLARE @IDVar INT, @NameVar VARCHAR(50);
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl);
WHILE @RowCount > 0 BEGIN
SELECT @IDVar=ID, @NameVar=[Name]
FROM @tbl
ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
SELECT @IDVar, @NameVar -- just to see
-- do whatever needed, apply any logic, call stored procedures, etc.
/*
Exec usp_Employee @name = @NameVar
Exec usp_Department @ID = @IDVar,@name = @NameVar
*/
SET @RowCount -= 1;
END
Курсоры существуют не просто так. Цикл WHILE не становится быстрее только потому, что в нем нет слова CURSOR, во всяком случае, он будет медленнее.
Возможно это поможет stackoverflow.com/questions/1656804/…