Stored Procedure - Invalid object name

[morechocolate]

I have a stored procedure where I create a table and the table that I am creating is a temporary table.
Like so:

create proc insr_asset_seg
AS
--create temporary table to take asset segment values
create table #temp_asset_seg
(
LOAN_NUMBER CHAR(16) NOT NULL,
PORT1 NUMERIC(11,8) NULL,
PORT2 NUMERIC(11,8) NULL,
PORT3 NUMERIC(11,8) NULL
)
go

When I tested this portion by it self, the stored procedure successfully creates. However, when I add the following statement I get Invalid object name '#temp_asset_seg'. Why is that? If I get rid of the stored procedure command it works fine.

--update temp_asset_seg with loan numbers
INSERT #temp_asset_seg
(LOAN_NUMBER)
SELECT DISTINCT P.QA_INV_ID
FROM MLMS_RES.INDEVL.LAST_PPR_PAS P
GO

Thanks

 

[MeanGreen]

Just get rid of the GO statement after the temp table creation:

create proc insr_asset_seg
AS
--create temporary table to take asset segment values
create table #temp_asset_seg
(
LOAN_NUMBER CHAR(16) NOT NULL,
PORT1 NUMERIC(11,8) NULL,
PORT2 NUMERIC(11,8) NULL,
PORT3 NUMERIC(11,8) NULL
)
--go
--update temp_asset_seg with loan numbers
INSERT #temp_asset_seg
(LOAN_NUMBER)
SELECT DISTINCT P.QA_INV_ID
FROM MLMS_RES.INDEVL.LAST_PPR_PAS P
GO

Hope this helps.

 

[morechocolate]

Thanks MeanGreen.

Something so simple.

mc