CODEWITHSUNDEEP
sql-serverstored-proceduresentity
Bao Le
Bao Le

Reputation: 71

How to insert data to temp Table in stored procedure with another procedure?

I want insert data to procedure 2 AddTempData from procedure 1 AddID1. But i can't do it. 

create proc AddID1
as
begin
declare @TempData table
(
    ID int
)
insert into @TempData select P.Id from Product as P 
select * from @TempData
end

create proc AddTempData
as
begin
declare @TempDataID table
(
  IDTemp int
)
insert into @TempDataID exec AddID1 
select * from @TempDataID
end

Upvotes: 0

Views: 46546

Answers (1)

granadaCoder
granadaCoder

Reputation: 27852

Below is a complete working example.

As far as your code.....maybe try

insert into @TempDataID (IDTemp)
   exec AddID1

But the below are working examples:

/*  START TSQL CODE */

/*  Stored Procedure Definition */

Use Northwind
GO


IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspOrderDetailsByCustomerId'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspOrderDetailsByCustomerId]
END


GO

CREATE Procedure dbo.uspOrderDetailsByCustomerId
(
  @CustomerID nchar(5)
)
AS

BEGIN

    SET NOCOUNT ON



        SELECT 
            c.CustomerID, c.CompanyName /*,c.ContactName,c.ContactTitle,c.[Address],c.City,c.Region,c.PostalCode,c.Country ,c.Phone,c.Fax */
        FROM 
            Customers c 
            JOIN Orders o ON c.CustomerID = o.CustomerID /* this join here just to provide extra rows for the example */
        WHERE 
            c.CustomerID = @CustomerID

END

Now the populate, first with a #Temp table.

    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end


    CREATE TABLE #TempCustomer
    ( 
      [CustomerID] nchar(5)
    , [CompanyName] nvarchar(40)
    )

INSERT INTO #TempCustomer ( [CustomerID] , [CompanyName])
exec dbo.uspOrderDetailsByCustomerId 'ALFKI'

Select * from #TempCustomer


    IF OBJECT_ID('tempdb..#TempCustomer') IS NOT NULL
    begin
            drop table #TempCustomer
    end

You can also use a @Variable Table ( like your example ) 

declare @VariableTableCustomer table
    ( 
      [CustomerID] nchar(5)
    , [CompanyName] nvarchar(40)
    )

INSERT INTO @VariableTableCustomer ( [CustomerID] , [CompanyName])
exec dbo.uspCustomerByCustomerId 'ALFKI'

Select * from @VariableTableCustomer

I just ran all your code (minus one line), it works:

--drop proc AddID1
GO

create proc AddID1
as
begin
declare @TempData table
(
    ID int
)


insert into @TempData 
/*select P.Id from Product as P */
/* note, i'm commenting out the above line and using the below line since I don't have your db table/rows */
select 333 union all select 444 union all select 555


select * from @TempData
end


GO



/* make sure hte procedure works...as you desire..before trying to stuff it into a temp or varaible table */
EXEC AddID1

GO




--drop proc AddTempData
GO


create proc AddTempData
as
begin
declare @TempDataID table
(
  IDTemp int
)
insert into @TempDataID (IDTemp) exec AddID1 
select 'Inside_AddTempData' as MyPlace , * from @TempDataID
end

GO

EXEC AddTempData

Upvotes: 1

Related Questions