Below, I show the tables I am working with and present the problem I am trying to solve.
-- 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 PROC [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;
How may I accomplish the desired invocations of [dbo].[My_Stored_Procedure] with each invocation passing one combination of values as described above?
3
Please check the following conceptual example.
Just insert result of your SELECT with CROSS JOINs into a table variable, and repeat the logic.
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