1
declare @temp table
(
    ItemID varchar(100),
)

INSERT INTO @temp (ItemID, Qt)
    EXEC A 'LA'

I want to then take the temp table results in field1 (will show field1 and field2) and loop those results using stored procedure B.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ThunderCatsHO
  • 19
  • 1
  • @temp denotes a `table variable`; that data will not be available outside of the stored procedure. What you need is a `global temp table`. Just change @temp to ##temp – Alan Burstein Apr 19 '18 at 21:07
  • Instead of looping you should change procedure B to receive a table valued parameter. Then you can pass in the entire set and not resort to looping. – Sean Lange Apr 19 '18 at 21:19

2 Answers2

0

Figured I'd provide an example (I was 99% sure my comment was correct but thought I'd double-check.

CREATE PROC dbo.A @x varchar(10) AS SELECT @x FROM (VALUES (1),(1),(1))x(x);
CREATE PROC dbo.B AS SELECT * FROM ##xx;

IF object_id('tempdb..##xx') IS NOT NULL DROP TABLE ##xx;
CREATE TABLE ##xx (col1 varchar(10));

INSERT ##xx EXEC dbo.A 'hi';

EXEC dbo.B;

Results:

col1 
------
hi
hi 
hi
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • 2
    I would stay away from global temp tables if at all possible. They all sorts of concurrency issues. Why not just a temp table? – Sean Lange Apr 19 '18 at 21:19
0

You could create a User-Defined Table Type, declare a variable as that type (instead of declaring a table variable) and send that over to a procedure which is expecting that type - as a READONLY.

For example:

CREATE TYPE SomeTableType AS TABLE(
    [Id] INT,
    [Val] VARCHAR(10)
)

Create a procedure that takes this type as a READONLY parameter:

CREATE PROCEDURE p_some_procedure_name
    @table SomeTableType READONLY
AS
    SELECT * FROM @table;

Now you can declare an instance of your type anywhere (say another procedure), populate it with data and call p_some_procedure_name with it:

DECLARE @temp_table SomeTableType;

INSERT @temp_table(Id, Val) VALUES (1, 'First'), (2, 'Second');

EXEC p_some_procedure_name @temp_table;

If possible (which is often the case), avoid looping.

NotAnAuthor
  • 1,071
  • 8
  • 9