multiple recordsets from sp

[sdh]

Please help I want to concatenate the results from
three recordsets returned from one procedure

the procedure is simply
ALTER PROCEDURE [dbo].[usp_keywordSearch]

(
@keywords varchar(2000)
)

AS
Begin

DECLARE @CRC int

-- Set parameter values
EXEC @CRC = [dbtophorse].[dbo].[usp_keywordSearchComment] @keywords

DECLARE @ARC int

-- Set parameter values
EXEC @ARC = [dbtophorse].[dbo].[usp_keywordSearchLineageAllAncestry] @keywords


DECLARE @SRC int

-- Set parameter values
EXEC @SRC = [dbtophorse].[dbo].[usp_keywordSearchSynopsis] @keywords

How can I concatenate the three recordsets this returns?

 

[vongrunt]

Make this last line in SP:

select @CRC as CRC, @ARC as ARC, @SRC as SRC

 

[sdh]

Thanks

Each recordset consists of two fields (returned from the exec statement) but when I follow vongrunt's method I get 1 record three fields all 0 can anyone explain

 

[vongrunt]

Oh... another problem then. Have you tried temporary table and INSERT INTO #tmptable EXEC procedure_call?

 

[hmckillop]

Vongrunt's way is probably the best, bar maybe rewriting the stored procedures to be one new one which uses UNION to return the 3 sets of data as 1


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

In your method you are trying to set a recordset with two fields equal to an integer variable. Of course you get nothing because these things don't match up. vongrunt had no way of knowing that from what you posted. His solution was correct for the information you posted and would work if each of these sps was returning a single record with one integer field.

The temp table idea or the union one should work in either event. I would probably have to try both to see which was more efficient.

Questions about posting. See faq183-874