You can simply this procedure to use 1 temporary table as below and it
should work just fine.
Jeff is write since you use the EXEC statement #TBillInv1 is dropped as soon
as EXEC finishes.
Below creates the table explicitly.  Uses a computed column to get the new
invoice.
and used the replicate in the update statement.  This should work.  You can
add back in the IF statements you need, but the basic framework should work.


CREATE PROCEDURE CEAcctInvNumberAssign
@BeginDate as Datetime,
@EndDate as Datetime,
@Cycle as Int = 1,
@SelectedRegions as Varchar(50) = Null,
@SystemListing as Varchar(100) = Null

AS



--This proceedure creates the invoice numbers based on the previous invoice
number
Declare @Binv as int
Declare @Stmt1 as Varchar(1000)
Declare @strCycle as varchar(6)
--Find out the starting point for the invoice numbering
Set @Binv = convert(int,(Select Max(Isnull(binv,0)) from bil))
--Create temporary table with seed of the first new invoice
CREATE TABLE #TBillInv1 (
	[ID] int IDENTITY (1, 1) NOT NULL ,
	Bccode varchar(60),
	CEC_ClientName varchar(50),
	BInvMax int,
	BInvNew as [ID]+BinvMax
	)

--Insert invoices to be assigned
--NOTE if any ordering needs to be done perform on this select statement
	insert into #TbillInv1 (Bccode,CEC_ClientName,BInvMax)
	SELECT distinct Bccode,CEC_ClientName, @Binv
	     FROM vAcctInvNoAssignforBilling 
	 Where (Bdate between  @BeginDate  and 
	 @EndDate) and 
	 (binv is null) and (CEC_Cycle = @Cycle)
-- now update the main table
Update bil 
	Set bil.Binv 
replicate('0',6-len(convert(varchar(6),#TbillInv1.BInvNew))) 
		+  convert(varchar(6),#TbillInv1.BInvNew)
from #TBillInv1 INNER JOIN
	Bil on #TbillInv1.BCCODE = bil.BCCode and (bdate  between @BeginDate
and  
@EndDate) and bil.binv is null

Brian Freeman
Carnegie Technology and Bluewave Computing
(xxx) xxx-xxxx  ext. 415
www.carnegie.com   / www.bluewave-computing.com


-----Original Message-----
From: Carolyn Friedberg [mailto:cfriedberg@c...]
Sent: Wednesday, October 16, 2002 3:44 PM
To: sql language
Subject: [sql_language] Temporary Tables - Help Please


I still have not got a way to work with the Temporary tables.  These 
statements execute and there is a result set returned.  But when the next 
statement  SELECT IDENTITY(int, 1, 1) AS RowNum, * INTO #TBillInv2 FROM 
#TBillInv1 is called, I get the error message "Invalid object 
name '#TBillInv1'."



Here's the code:

CREATE PROCEDURE CEAcctInvNumberAssign
@BeginDate as Datetime,
@EndDate as Datetime,
@Cycle as Int = 1,
@SelectedRegions as Varchar(50) = Null,
@SystemListing as Varchar(100) = Null

AS

--This proceedure creates the invoice numbers based on the previous 
invoice number
Declare @Binv as Varchar(6)
Declare @Stmt1 as Varchar(1000)
--Find out the starting point for the invoice numbering
Set @Binv = (Select Max(Isnull(binv,0)) from bil)


if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID
(N'tempdb.. #TBillInv1 ')) 
drop table  #TBillInv1  

--Use a view for getting the proper sort to begin with.
If @SystemListing is not null
	Begin
	if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID
(N'tempdb..#TBillInv1')) 
	drop table #TBillInv1 
	Set @Stmt1 = '	SELECT distinct Bccode,CEC_ClientName,Binv ' +
		     ' INTO #TBillInv1 ' +
		     ' FROM vAcctInvNoAssignforBilling ' +
		     'Where (Bdate between ' + Char(39) + 
                      Convert(varchar, @BeginDate,101) + Char(39) +
                     ' and ' + Char(39) +Convert(varchar,@EndDate,101) 
                      +   Char(39) +') and ' +
		     '  (binv is null) ' + ' and (CEC_Cycle = ' + 
                      Convert(varchar,@Cycle) + ') and ' +
		     ' Bccode IN (Select BCCode from Bil) '
	End

If @SystemListing is null
	Begin
	if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID
(N'tempdb.. #TBillInv1 ')) 
	drop table  #TBillInv1  

	Set @Stmt1 = '	SELECT distinct Bccode,CEC_ClientName,Binv ' +
		     ' INTO #TBillInv1 ' +
		     ' FROM vAcctInvNoAssignforBilling ' +
		     'Where (Bdate between ' + Char(39) + 
                     Convert(varchar, @BeginDate,101) + Char(39) +
                    ' and ' +  Char(39) +
                     Convert(varchar,@EndDate,101)+  Char(39) +') and ' +
		    '  (binv is null) ' + ' and (CEC_Cycle = ' + 
                     Convert(varchar,@Cycle) + ')'
	End

Exec (@Stmt1)
/*
if exists (select * from [tempdb]..sysobjects where id = OBJECT_ID
(N'tempdb.. #TBillInv1 ')) 
drop table  #TBillInv1  
--Choose the @SystemListing over the regions
If @SystemListing is null and @SelectedRegions is not null
	Begin
		SELECT distinct Bccode,CEC_ClientName,Binv 
		     INTO #TBillInv1 
		     FROM vAcctInvNoAssignforBilling 
		 Where (Bdate between  @BeginDate  and 
		 @EndDate) and 
		 (binv is null) and (CEC_Cycle = @Cycle)
	End
  
*/
--Because ordering is not possible with these types of temporary tables, 
--create an identity column as RowNum for the basis and the view for the 
actual sort.
--The goal is to have the invoices be produced in alphbetical order by 
company.
   SELECT IDENTITY(int, 1, 1) AS RowNum, *
     INTO #TBillInv2
     FROM #TBillInv1