Save a Query as stored procedure and let it iterate through an array

Question

I want to run my query multiple times, based on values in array. I came quite close, but I don't know how to call my query through the iteration. The examples I find online state 'Exec procedure' but then don't show the content of a procedure, so I don't know how to let a query refer to a procedure the right way.

In my code below I first have show the iteration and then I have a procedure that I named QueryIWantToUse.


INSERT INTO @ids (id)
    SELECT 'CTMContactOrigin' UNION 
    SELECT 'PMMParameters' UNION 
    SELECT 'TAXTABLE'
DECLARE @i INT
DECLARE @cnt INT

SELECT @i = MIN(idx) - 1, @cnt = MAX(idx) FROM @ids

WHILE @i < @cnt
BEGIN
     SELECT @i = @i + 1

     DECLARE @id = SELECT id FROM @ids WHERE idx = @i

     EXEC QueryIWantToUse @id
END

--QueryIWantToUse:
DECLARE @Table      nvarchar(max),
        @EntityName nvarchar(max),
        @EntityLeaf nvarchar(max),
        @EntityCode nvarchar(10),
        @CodeRefTable_EN nvarchar(max),
        @CodeRefTable_MS nvarchar(max),
        @sql  nvarchar(max);
SET     @Table = @id -- Here I want the array values to be used somehow
SET     @EntityName = 
            (SELECT ENTITYTABLE FROM [Tobias_AX_MasterZWS_test].[dbo].[DMFENTITY] --MASTER DB NAAM
            WHERE [Tobias_AX_MasterZWS_test].[dbo].[DMFENTITY].ENTITYTYPENAME = @Table
            )
SET     @EntityLeaf = @EntityName + '_Leaf'
SET     @EntityCode = 
            (SELECT Code FROM [MDS_AX2012_ZWS].[mdm].[tbl_1_2_EN]
            WHERE [MDS_AX2012_ZWS].[mdm].[tbl_1_2_EN].[NAME] = @EntityName
            )
SET     @CodeRefTable_EN = 'tbl_'+@EntityCode +'_EN'
SET     @CodeRefTable_MS = 'tbl_'+@EntityCode +'_MS'
DECLARE @TableName_EN sysname = @CodeRefTable_EN,
        @TableName_MS sysname = @CodeRefTable_MS;


SET @sql =      N'SELECT * FROM [MDS_AX2012_ZWS].[dbo].[AxVersionTable] WHERE [MDS_AX2012_ZWS].[dbo].[AxVersionTable].EntityName ' + --change to DELETE FROM after testing
                N' = ' +
                quotename(@EntityName, '''') +
                N';';
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [MDS_AX2012_ZWS].[STG].' + quotename(@EntityLeaf) --change to DROP TABLE after testing
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [MDS_AX2012_ZWS].[MDM].' + quotename(@TableName_EN) + 'WHERE VERSION_ID <> 1337'; --change to DELETE FROM after testing
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [MDS_AX2012_ZWS].[MDM].' + quotename(@TableName_MS) + 'WHERE VERSION_ID <> 1337'; --change to DELETE FROM after testing
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [MDS_AX2012_ZWS].[MDM].' + quotename(@TableName_EN); --change to DROP TABLE after testing
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [MDS_AX2012_ZWS].[MDM].' + quotename(@TableName_MS); --change to DROP TABLE after testing
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [Tobias_AX_MasterZWS_test].[dbo].' + quotename(@EntityName); --change to TRUNCATE TABLE after testing
EXEC sp_executesql @sql;
SET @sql =      N'SELECT * FROM [Tobias_AX_MasterZWS_test].[dbo].[DMFENTITY] WHERE [Tobias_AX_MasterZWS_test].[dbo].[DMFENTITY].EntityTable ' + --change to DELETE FROM after testing
                N' = ' +
                quotename(@EntityName, '''') +
                N';';
EXEC sp_executesql @sql;

Any suggestions how to link the iteration with the procedure?

Have you considered making "QueryIWantToUse" a stored procedure? Then your looping code could call the stored procedure. — STLDev, Sep 23 '19 at 22:04
Googling on stored procedure solved my problem via: https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure and https://stackoverflow.com/questions/38388144/iterate-through-a-list-of-strings-in-sql-server — titatovenaar, Sep 23 '19 at 22:47
Excellent - I thought it might. Best of luck with your new-found skill! — STLDev, Sep 23 '19 at 22:52

Save a Query as stored procedure and let it iterate through an array

0 Answers0