SQL Server How to output multiple results into one table

Question

I hope you are all ok, Although this question is similar, or for some people completely identical.But I haven't been able to reproduced the same output. This is my stored procedure. You can see the original stored procedure here.

In the While section is the section that returns the values, but it's returning the results separated. Like this:

ImageInHere

What I want is this:

Select * 
From [DynaForms].[dbo].[Enums_Tables] 
Where id In (Select parsename(@tmpTblname, 1) 
             From SearchTMP)

to put all results in the same table instead of being separated

ALTER PROCEDURE [dbo].[SP_SearchTables] 
    @SearchStr NVARCHAR(60),
    @GenerateSQLOnly Bit = 0,
    @SchemaNames VARCHAR(500) ='%' 
AS
    SET NOCOUNT ON 

    DECLARE @MatchFound BIT 

    SELECT @MatchFound = 0 

    DECLARE @CheckTableNames TABLE (Schemaname sysname, Tablename sysname) 

    DECLARE @SearchStringTbl TABLE (SearchString VARCHAR(500)) 

    DECLARE @SQLTbl TABLE (Tablename SYSNAME,
                           WHEREClause VARCHAR(MAX),
                           SQLStatement VARCHAR(MAX),
                           Execstatus BIT) 

    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @TableParamSQL VARCHAR(MAX) 
    DECLARE @SchemaParamSQL VARCHAR(MAX) 
    DECLARE @TblSQL VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 
    DECLARE @ErrMsg VARCHAR(100)

    IF LTRIM(RTRIM(@SchemaNames)) ='' 
    BEGIN 

        SELECT @SchemaNames = '%' 
    END  

    IF CHARINDEX(',',@SchemaNames) > 0  
        SELECT @SchemaParamSQL = 'SELECT ''' + REPLACE(@SchemaNames,',','''as SchemaName UNION SELECT ''') + '''' 
    ELSE 
        SELECT @SchemaParamSQL = 'SELECT ''' + @SchemaNames + ''' as SchemaName ' 


        SELECT @TblSQL = 'SELECT SCh.NAME,T.NAME 
                  FROM SYS.TABLES T 
                  JOIN SYS.SCHEMAS SCh 
                  ON SCh.SCHEMA_ID = T.SCHEMA_ID 
                  INNER JOIN [DynaForms].[dbo].[Enums_Tables] et on 
                     (et.Id = T.NAME COLLATE Latin1_General_CI_AS)  '            


        INSERT INTO @CheckTableNames 
        (Schemaname,Tablename) 
        EXEC(@TblSQL)  

    IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
    BEGIN 

        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN  
    END  

    IF LTRIM(RTRIM(@SearchStr)) ='' 
    BEGIN 

        SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter' 
        PRINT @ErrMsg 
        RETURN 
    END 
    ELSE 
    BEGIN  
        SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#') 
        SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#') 

        SELECT @SearchStr = REPLACE(@SearchStr,'''','''''') 

        SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + '''' 

        INSERT INTO @SearchStringTbl 
        (SearchString) 
        EXEC(@SQL) 

        UPDATE @SearchStringTbl 
           SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',') 
    END 

    INSERT INTO @SQLTbl 
    ( Tablename,WHEREClause) 
    SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), 
            ( 
                SELECT '[' + SC.Name + ']' + ' LIKE ''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10) 
                  FROM SYS.columns SC 
                  JOIN SYS.types STy 
                    ON STy.system_type_id = SC.system_type_id 
                   AND STy.user_type_id =SC.user_type_id 
                  CROSS JOIN @SearchStringTbl SearchSTR 
                 WHERE STY.name in ('varchar','char','nvarchar','nchar','text') 
                   AND SC.object_id = ST.object_id 
                 ORDER BY SC.name 
                FOR XML PATH('') 
            ) 
      FROM  SYS.tables ST 
      JOIN @CheckTableNames chktbls 
        ON chktbls.Tablename = ST.name  
      JOIN SYS.schemas SCh 
        ON ST.schema_id = SCh.schema_id 
       AND Sch.name        = chktbls.Schemaname 
     WHERE ST.name <> 'SearchTMP' 
      GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ; 


      UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 

      DELETE FROM @SQLTbl 
       WHERE WHEREClause IS NULL 

    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 

        SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
          FROM @SQLTbl  
         WHERE ISNULL(Execstatus ,0) = 0 

         IF @GenerateSQLOnly = 0 
         BEGIN 

            IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
                DROP TABLE SearchTMP 

            EXEC (@SQL) 


            IF EXISTS(SELECT 1 FROM SearchTMP) 
            BEGIN 
                --SELECT parsename(@tmpTblname,1) FROM SearchTMP 
                SELECT @MatchFound = 1 
                Select * from [DynaForms].[dbo].[Enums_Tables] where id in (SELECT parsename(@tmpTblname,1) FROM SearchTMP )
            END 

         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@SQL,'INTO SearchTMP','') 
         END 

         UPDATE @SQLTbl 
            SET Execstatus = 1 
          WHERE Tablename = @tmpTblname 
    END 

    IF @MatchFound = 0  
    BEGIN 
        SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 

    SET NOCOUNT OFF 

What I tried to solve this problem: I was trying to make unions, but I haven't being able to achieve the desired outcome. Could you please, help me with this, please?

Answer 1

You need to create a temp table/table variable to hold the results of each execution within the while loop (as they are executing as separate batches, which will always return as separate result sets).

DECLARE @output TABLE (id VARCHAR(200), name VARCHAR(200)) 

    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN
    ...

        SELECT @MatchFound = 1

        INSERT INTO @output (id, name)
        Select * from [DynaForms].[dbo].[Enums_Tables] where id in (SELECT parsename(@tmpTblname,1) FROM SearchTMP )

    ...

    END 

    ...

SELECT * FROM @output

This will only work if the resultset from each select statement is the same (e.g. always id, name), plus check the datatypes are correct as I've had to guess.