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

Question

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;

Answer 1

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.