Multi-tables joining in Cursor

Question

I got a table of words to be searched in the database and the table

Parameter

id     Name              Word
----------------------------------
1      word search       c&a
2      word search       Beton
3      word search       Freiman
4      Anything          Null

beside that i have the Procedure SearchAllTable, which is the searching Procedure and for searching the words in the database.

DECLARE @id int
DECLARE @name varchar(100)
DECLARE @word varchar(100)

DECLARE cur CURSOR FOR SELECT Id, word FROM #Parameter WHERE Name = 'word search'
OPEN cur

FETCH NEXT FROM cur INTO @id,@word

WHILE @@FETCH_STATUS = 0 BEGIN
EXEC SearchAllTables @word
FETCH NEXT FROM cur INTO @id, @word
END
CLOSE cur    
DEALLOCATE cur

the problem that I got the result in multipule tables and I want them all to be listed in one table without any suppuration.

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

Insert your results into temporary table, created before cursor cycle

UPDATE

try this UNION the results of multiple stored procedures

edited May 23 '17 at 10:28

Community

1
1

answered Jul 14 '16 at 07:01

xdd

535
2
4

I already tried but for some reasons it is not working 'SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC SearchAllTables @word')' – Chis Jul 14 '16 at 07:50
As i understand you can't modify `SearchAllTables` or rewrite it as function? Then probably you should ask new question about problem with `openrowset`. And wait if someone advice something new in that question. May be this one? http://stackoverflow.com/questions/5292069/union-the-results-of-multiple-stored-procedures – xdd Jul 14 '16 at 08:50

score 0 · Answer 2 · answered Jul 14 '16 at 11:47

Collect your result into a temp table or table variable and get it in a single select outside the cursor. Please try the bellow.

DECLARE @id int
DECLARE @name varchar(100)
DECLARE @word varchar(100)
--Create Result table wrt your SP output
DECLARE @Result TABLE (NAME VARCHAR(16), DATEVAL DATETIME)


DECLARE cur CURSOR FOR SELECT Id, word FROM @Parameter WHERE Name = 'word search'
OPEN cur
    FETCH NEXT FROM cur INTO @id,@word

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT INTO @Result
        EXEC SearchAllTables @word

        FETCH NEXT FROM cur INTO @id, @word
    END
CLOSE cur    
DEALLOCATE cur

--Selecting result as a single unit
SELECT * FROM @Result

It is not working An INSERT EXEC statement cannot be nested. — Chis, Jul 15 '16 at 07:22

Multi-tables joining in Cursor

Parameter

2 Answers2