Problem getting output of sp_executesql into a table column (SQL SERVER)-CodePudding

I have a table called QUERY_TABLE. QUERY_TABLE contains queries of the following format (nothing dynamic, except the parameters in the WHERE clause):

  SELECT
     CASE
         WHEN COUNT(*) > 10 THEN 'Y'
         ELSE 'N'
     END check_ind
FROM
     DATA_TABLE 
WHERE date_y = @DATE_Y AND date_m = @DATE_M

I wrote the following script, to fetch each one of the above queries, run it, and put the result into another table - CONTROL_LOG_TABLE.

My problem is that the result column, STATUS_IND, should get either 'Y' or 'N' as values, but for some reason I have not figured out yet, it contains '0'.

Appreciate your help!

DECLARE 
       @DATE_C DATE,
       @DATE_Y           INTEGER,
       @DATE_M           INTEGER,
       @CHECK_NUM        INTEGER,
       @CHECK_ID         INTEGER, 
       @CTRLM_TREE       VARCHAR(50),
       @CTRLM_TREE_PARAM VARCHAR(50),
       @SQL_QUERY        NVARCHAR(MAX),
       @CHECK_DESC       NVARCHAR(MAX),
       @ACTION_DESC      NVARCHAR(MAX),
       @EXEC_SQL_QUERY   NVARCHAR(MAX),
       @RESULT_SQL       CHAR(1),
       @RowNo            INTEGER,
          @params NVARCHAR(100) = '@DATE_Y NVARCHAR(4), @DATE_M NVARCHAR(2)';
          

BEGIN

SET @RowNo = 0;
SET @DATE_C = GETDATE();
SET @DATE_Y = (SELECT YEAR (@DATE_C));
SET @DATE_M = (SELECT MONTH (@DATE_C));

DECLARE CURSOR_CHECK_ID CURSOR
FOR SELECT 
       CHECK_ID,
       CTRLM_TREE,
       SQL_QUERY,
       CHECK_DESC,
       ACTION_DESC 
    FROM 
       QUERY_TABLE
          

OPEN CURSOR_CHECK_ID;
FETCH NEXT FROM CURSOR_CHECK_ID INTO 
            @CHECK_ID, 
            @CTRLM_TREE,
            @SQL_QUERY,
            @CHECK_DESC,
            @ACTION_DESC;
       WHILE @@FETCH_STATUS = 0
    BEGIN
           SET @RowNo = @RowNo 1
           SET @CHECK_NUM = @RowNo
           SET @EXEC_SQL_QUERY = @SQL_QUERY

                       EXECUTE @result_sql = sp_executesql @EXEC_SQL_QUERY, @params, @DATE_Y, @DATE_M
             
        INSERT INTO CONTROL_LOG_TABLE (UPDATE_DATE, DATE_C, CHECK_NUM, CHECK_ID, CTRLM_TREE, SQL_QUERY,
              CHECK_DESC, ACTION_DESC, STATUS_IND)
              values (GETDATE(), @DATE_C, @RowNo, @CHECK_ID, @CTRLM_TREE, @SQL_QUERY, @CHECK_DESC, @ACTION_DESC, @RESULT_SQL)


        FETCH NEXT FROM CURSOR_CHECK_ID INTO 
            @CHECK_ID, 
            @CTRLM_TREE,
            @SQL_QUERY,
            @CHECK_DESC,
            @ACTION_DESC;
    END;
CLOSE CURSOR_CHECK_ID;
DEALLOCATE CURSOR_CHECK_ID;
END;

CodePudding user response：

To capture a resultset from a dynamic batch use INSERT...EXECUTE to load a temp table or table variable. EG

    declare @r table (check_ind char(1))

    insert into @r(check_ind) 
    EXECUTE sp_executesql @EXEC_SQL_QUERY, @params, @DATE_Y, @DATE_M
         
    INSERT INTO CONTROL_LOG_TABLE (UPDATE_DATE, DATE_C, CHECK_NUM, CHECK_ID, CTRLM_TREE, SQL_QUERY,
          CHECK_DESC, ACTION_DESC, STATUS_IND)
          values (GETDATE(), @DATE_C, @RowNo, @CHECK_ID, @CTRLM_TREE, @SQL_QUERY, @CHECK_DESC, @ACTION_DESC, (select check_ind from @r) )

CodePudding user response：

One solution is to move the INSERT into the dynamic query.

You don't need to actually change those queries, you can just concatenate the INSERT around it.

SET @EXEC_SQL_QUERY = '
        INSERT INTO CONTROL_LOG_TABLE (UPDATE_DATE, DATE_C, CHECK_NUM, CHECK_ID, CTRLM_TREE, SQL_QUERY,
              CHECK_DESC, ACTION_DESC, STATUS_IND)
              values (GETDATE(), @DATE_C, @RowNo, @CHECK_ID, @CTRLM_TREE, @SQL_QUERY, @CHECK_DESC, @ACTION_DESC, ('   @SQL_QUERY   ')';
';

Another way of doing it is to concatenate SET @outputvariable to it:

SET @EXEC_SQL_QUERY = 'SET @result = ('   @SQL_QUERY   ');
';

EXECUTE sp_executesql @EXEC_SQL_QUERY, @params, @DATE_Y, @DATE_M, @result_sql;
             
        INSERT INTO CONTROL_LOG_TABLE (UPDATE_DATE, DATE_C, CHECK_NUM, CHECK_ID, CTRLM_TREE, SQL_QUERY,
              CHECK_DESC, ACTION_DESC, STATUS_IND)
              values (GETDATE(), @DATE_C, @RowNo, @CHECK_ID, @CTRLM_TREE, @SQL_QUERY, @CHECK_DESC, @ACTION_DESC, @RESULT_SQL)

and you would need to add that output parameter to @params also.

I'm assuming you have all your bases covered with SQL injection here.