时间戳

首页 > 解决方案 > 存储过程只返回表 1 的数据，而不是 10 个表

sql-server - 存储过程只返回表 1 的数据，而不是 10 个表

问题描述

ALTER PROCEDURE [dbo].[ProcessListSQL]  
    ( --@CommaDelimitedList AS NVARCHAR(MAX),
     @SQLtoExecute AS NVARCHAR(MAX),
     @Result VARCHAR(MAX) OUTPUT)
AS 
BEGIN
    DECLARE @Statements TABLE
                        (PK INT IDENTITY(1,1) PRIMARY KEY,
                         SQLObject NVARCHAR (MAX)
                        )

    SET @SQLtoExecute = REPLACE (@SQLtoExecute, '"', '''')
    /*================*/
    DECLARE @CommaDelimitedList  AS NVARCHAR(MAX)
    DECLARE @ddd NVARCHAR(MAX)
    DECLARE @R1 NVARCHAR(MAX)

    SET @ddd=''

    SELECT @ddd = @ddd + organisation  + ',' 
    FROM dbo.Orgs;

    SET @CommaDelimitedList = @ddd   /*select all tables that need to be interrogated*/

    /*================*/
   INSERT INTO @Statements
       SELECT PARAM 
       FROM [dbo].[fn_MVParam](@CommaDelimitedList,',')

    DECLARE @i INT
    SELECT @i = MIN(PK) FROM @Statements

    DECLARE @max INT
    SELECT @max = MAX(PK) FROM @Statements

    DECLARE @SQL AS NVARCHAR(MAX) = NULL
    DECLARE @Object AS NVARCHAR(MAX) = NULL
 
    WHILE @i <= @max 
    BEGIN       
        SELECT @Object = SQLObject FROM @Statements WHERE PK = @i        /*gets the table from the array table*/
        SET @SQL = REPLACE(@SQLtoExecute, '{RP}', @Object)               /*replaces RP with tablename */
        -- Uncommend below to check the SQL
        -- PRINT @SQL
        DECLARE @returnstatus nvarchar(15);     /*mh did this */
        SET @returnstatus = NULL;               /*mh did this */

        EXECUTE sp_executesql  @SQL,@R1=@R1
        SET @Result = @Result + @R1
        SELECT @Object = NULL
        SELECT @SQL = NULL
        SET @i = @i + 1
    END
END

这工作正常，但只返回表集合来自函数的第一个表。

标签： sql-server

解决方案

我能做到这一点的最好方法是从过程 B 调用过程 A。在过程 B 中，将结果插入临时表并选择临时表，然后从 MVC c# 调用该临时表作为标准存储过程。

推荐阅读