将sp_executesql的输出获取到表列(SQL SERVER)时出现问题

Question

我有一个名为QUERY_table的表。QUERY_TABLE包含以下格式的查询(除了WHERE子句中的参数外，没有任何动态查询(：

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

我编写了以下脚本，以获取上面的每个查询，运行它，并将结果放入另一个表CONTROL_LOG_table中。

我的问题是，结果列STATUS_IND应该获得"Y"或"N"作为值，但由于某种原因，我还没有弄清楚，它包含"0"。

感谢你的帮助！

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

一种解决方案是将INSERT移动到动态查询中。

您不需要实际更改这些查询，只需将其周围的INSERT连接即可。

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 + ')';
';

另一种方法是将SET @outputvariable连接到它：

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)

并且您还需要将该输出参数添加到CCD_ 4中。

我假设您在这里已经用SQL注入覆盖了所有的基础。

Answer 2

要从动态批处理中捕获结果集，请使用INSERT。。。EXECUTE加载临时表或表变量。例如

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) )