How do I display multiple resultsets

[LonnieJohnson]

I have a stored procedure that I am using that searches all fields of all tables in a database to see if a string exist as data. So if I wanted to know if the word "Einstein" was an actual data value in any field of any table this SPROC would list all records of all tables that had it. It basically does a SELECT with a WHERE evaluating each field in the table.

What I would like to know now is how can I get the results to show in a report or application. Is it possible to render multiple results from such a SPROC somewhere other than the results panel of SSMS?

Thanks.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[bborissov]

You could create a Temp table and insert the results in it with the name of the queried table, then just SELECT from that temp table.
Something like:

CREATE TABLE #TempResult (TableName varchar(200), FieldName varchar(200), OtherInfo theType)

INSERT INTO #TempResult ...
SELECT 'Table1'
       ,'Field1'
       ,otherInfo
FROM Table1 
WHERE Field1 LIKE '%Einstein%'

INSERT INTO #TempResult ...
SELECT 'Table1'
       ,'Field2'
       ,otherInfo
FROM Table1 
WHERE Field2 LIKE '%Einstein%'
...
INSERT INTO #TempResult ...
SELECT 'TableN'
       ,'FieldN'
       ,otherInfo
FROM TableN
WHERE FieldN LIKE '%Einstein%'

SELECT * FROM #TempResult 
DROP TABLE #TempResult

Borislav Borissov
VFP9 SP2, SQL Server

 

[SaltyTheFrog]

You mentioned capturing stored procedure results. Same principle, just a little different syntax.

Stored procedure:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FindEinstein] AS

CREATE TABLE #TempResult (TableName varchar(200), FieldName varchar(200), OtherInfo varchar(10))

INSERT INTO #TempResult
SELECT 'Table1'
       ,'Field1'
       ,'otherInfo'
       
select * from #TempResult

GO

Capture results:

CREATE TABLE #SPROC_Results (TableName varchar(200), FieldName varchar(200), OtherInfo varchar(10))

INSERT INTO #SPROC_Results
exec FindEinstein

INSERT INTO #SPROC_Results
exec FindEinstein

INSERT INTO #SPROC_Results
exec FindEinstein

select * from #SPROC_Results