Transact-SQL
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
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
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...
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.