Returning resulsets between procedures !!

Hello, I need to do the following, But I receive an error:

CREATE PROCEDURE dbo.EJEMPLO_A AS
BEGIN
SELECT TOP 10 NAME, ID FROM SYSCOLUMNS
END
-- and Later use the one above... like this...

CREATE PROCEDURE dbo.EJEMPLO_B AS
BEGIN

DECLARE @.temp_lista TABLE (NOMBRE VARCHAR(500),
IDENT NUMERIC(5))

INSERT INTO @.temp_lista execute dbo.EJEMPLO_JULIAN

END

Pd. I just have to capture the dbo.EJEMPLO_A resulset... and I can't modify It

Jortiz:

You have to use a temp table instead of a table variable to receive your data. create a temp table instead of declaring a table variable; you are not allowed to use a table variable for what you are attempting.

Dave

|||Hello, could you please provide me an Example ? or tell me how to do it ?|||I got to Do It using a temporary table... But... is there any possibility to use a table variable or a Cursor ?
|||

Jortiz:

I think in this case it would really be more appropriate to convert your stored procedure to an inline function -- something like:

create function dbo.EJEMPLO_A ()
returns table
as
return (SELECT TOP 10 NAME, ID FROM SYSCOLUMNS)

Also, you cannot return it into a table variable and your are usually going to be better off avoiding a cursor-based solution; moreover, you cannot use the INSERT INTO ... EXEC PROC construct on more than one nesting level. You are really better off with the inline function solution.

Dave

|||Hi Dave, thank you so much for your answer... The problem here Is that I can't modify the dbo.EJEMPLO_A procedure, I just can change the second one...
|||

jortiz wrote:

Hi Dave, thank you so much for your answer... The problem here Is that I can't modify the dbo.EJEMPLO_A procedure, I just can change the second one...

I mean ... I have to do in the second procedure something like SELECT dbo.EJEMPLO_A

Thanks
|||

Jortiz:

OK. Then I think best option is probably to use a temp table to receive the data. The code for that would be something like:

CREATE TABLE #receiver
( column1
...
columnN
)

INSERT INTO #receiver
EXEC EJEMPLO_A

After this INSERT ... EXEC the #receiver table will be populated with the values returned from the stored procedure. If you could create an alternate function version of EJEMPLOY_A -- say a function named EJEMPLOY_Af the select to return the values from that function would simply be something like:

SELECT * FROM EJEMPLOY_Af ()

in addition, you would be able to join to that function similar to the way that you would join to a table.

|||I think I'll have to do that !!!

Thanks !!