There is already an object named '#temp1' in the database

jitganguly
jitganguly used Ask the Experts™
on
This is my SP.

CREATE PROCEDURE up_Tickler_Report5
@clauseis varchar(20)=null,
@srptmonth datetime,
@alpha varchar(5)='A',
@sort varchar(5)='Asc'
AS

set nocount on

if @clauseis <> 'Swap'
begin

     select d.deal_mid, d.deal_name, max(issuedate) as issuedate
     into #temp1
     from bbtsposition b, Deal_Master d
     where      d.deal_mid=b.deal_mid and
     issuedate is not null and
     upper(b.sec_type)=@clauseis  AND
     datediff(month,issuedate,@srptmonth) >= 0
     group by d.deal_mid,d.deal_name
end
else
begin

     select d.deal_mid, d.deal_name, max(b.trade_date) as issuedate
     into #temp1
     from swap_info b, Deal_Master d
     where d.deal_mid=b.deal_mid and
     b.trade_date is not null and
     datediff(month,b.trade_date,@srptmonth) >= 0
     group by d.deal_mid,d.deal_name

end

select distinct deal_mid, Report_Date, PDF_received_date,comments
into #temp2
from deal_info
where datediff(month,report_date,@srptmonth) = 0
select max(h.deal_mid)as max_mid,max(m.Deal_name) as max_name,h.report_date,min(h.update_dt) as min_dt
into #temp3
from Deal_Info_change_history h,Deal_Master m
where m.Deal_MID =  h.deal_mid and
h.comments = 'Data Modified'
and datediff(month,h.report_date,@srptmonth) = 0
group by h.deal_mid,m.Deal_name,h.report_date
order by m.deal_name,h.report_date
if @alpha <> 'All'
begin
if @sort='desc'
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     and left(t1.deal_name,1) = @alpha
     group by t1.deal_name, t2.report_date
     order by t1.deal_name desc, t2.report_date asc
end
else
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     and left(t1.deal_name,1) = @alpha
     group by t1.deal_name, t2.report_date
     order by t1.deal_name asc, t2.report_date asc
end
end

else
begin
if @sort='desc'
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     group by t1.deal_name, t2.report_date
     order by t1.deal_name desc, t2.report_date asc
end
else
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     group by t1.deal_name, t2.report_date
     order by t1.deal_name asc, t2.report_date asc
end
end


WHen I try to create it from Query Analyzer (SQL 7) it says "There is already an object named '#temp1' in the database"
I have a if condition based on input parameter. So #temp1 should be created only once. Why is it trying to create twice ? (I guess thats the problem )
Something wrong with my if-else construct ?

Suggestions ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Declare your #temp table in the begining of program and then use Insert before select>

INSERT INTO #temp (....)
SELECT ....

Author

Commented:
Why do I need to declare #temp ? For other temps e.g. temp2 temp3 I didn't do anything. Even if I take off the if-else clause for first temp1 it works fine.

I thought #temp is something you do not need to declare

Commented:
You don't need to declare temp tables, however, you should drop them.

As a matter of cleanup, try putting

Drop table #temp1
Drop table #temp2
Drop Table #temp3

At the end of your transaction.  

Out of an abundance of caution (incase a previous transaction did not complete), also put the folowing statements prior to creating the tables (in this case, your select into statement).

if exists (select *
               from tempdb.dbo.sysobjects
               where id = object_id('tempdb.dbo.#temp1'))
drop table #temp1



Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Temp tables (#name) do have to be declared.  You're doing it implicitly in the above code by using SELECT INTO, which tells SQL "declare a new table containing the columns in the SELECT below and then populate that table using the SELECT below".  

You need to check for the temp table and if it exists delete it, like so:

IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
    DROP TABLE #temp1

You should explicitly check because it's possible that another procedure the same user ran could have created a #temp1 with a different structure.

For efficiency reasons, you really should create and populate your table in two separate steps, like so:

--create the #temp1 table structure only, don't add rows
SELECT d.deal_mid, d.deal_name, max(issuedate) AS issuedate
INTO #temp1
FROM bbtsposition b, Deal_Master d
WHERE d.deal_mid=b.deal_mid and 1 = 0
--load the #temp1 table
INSERT INTO #temp1
select d.deal_mid, d.deal_name, max(issuedate) as issuedate
from bbtsposition b, Deal_Master d
where      d.deal_mid=b.deal_mid and
issuedate is not null and
    upper(b.sec_type)=@clauseis  AND
    datediff(month,issuedate,@srptmonth) >= 0
    group by d.deal_mid,d.deal_name

Ideally, you should do the same thing for any temp table (#temp2, #temp3, etc.).

Author

Commented:
As you all advised, I just put in drop table #temp1. But still getting the same error
"There is already an object named '#temp1' in the database."

CREATE PROCEDURE up_Tickler_Report5
@clauseis varchar(20)=null,
@srptmonth datetime,
@alpha varchar(5)='A',
@sort varchar(5)='Asc'
AS

set nocount on
if exists (select *
              from tempdb.dbo.sysobjects
              where id = object_id('tempdb.dbo.#temp1'))
drop table #temp1

if @clauseis <> 'Swap'
begin
     SELECT d.deal_mid, d.deal_name, max(issuedate) AS issuedate
     INTO #temp1
     FROM bbtsposition b, Deal_Master d
     WHERE d.deal_mid=b.deal_mid and 1 = 0
     --load the #temp1 table
     INSERT INTO #temp1
     select d.deal_mid, d.deal_name, max(issuedate) as issuedate
     from bbtsposition b, Deal_Master d
     where d.deal_mid=b.deal_mid and
     issuedate is not null and
     upper(b.sec_type)=@clauseis  AND
     datediff(month,issuedate,@srptmonth) >= 0
      group by d.deal_mid,d.deal_name
end
else
begin

     select d.deal_mid, d.deal_name, max(b.trade_date) as issuedate
     into #temp1
     from swap_info b, Deal_Master d
     where d.deal_mid=b.deal_mid and 1=0
     Insert into #temp1
     select d.deal_mid, d.deal_name, max(b.trade_date) as issuedate
     from swap_info b, Deal_Master d
     where d.deal_mid=b.deal_mid and
     b.trade_date is not null and
     datediff(month,b.trade_date,@srptmonth) >= 0
     group by d.deal_mid,d.deal_name
end

select distinct deal_mid, Report_Date, PDF_received_date,comments
into #temp2
from deal_info
where datediff(month,report_date,@srptmonth) = 0
select max(h.deal_mid)as max_mid,max(m.Deal_name) as max_name,h.report_date,min(h.update_dt) as min_dt
into #temp3
from Deal_Info_change_history h,Deal_Master m
where m.Deal_MID =  h.deal_mid and
h.comments = 'Data Modified'
and datediff(month,h.report_date,@srptmonth) = 0
group by h.deal_mid,m.Deal_name,h.report_date
order by m.deal_name,h.report_date
if @alpha <> 'All'
begin
if @sort='desc'
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     and left(t1.deal_name,1) = @alpha
     group by t1.deal_name, t2.report_date
     order by t1.deal_name desc, t2.report_date asc
end
else
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     and left(t1.deal_name,1) = @alpha
     group by t1.deal_name, t2.report_date
     order by t1.deal_name asc, t2.report_date asc
end
end

else
begin
if @sort='desc'
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     group by t1.deal_name, t2.report_date
     order by t1.deal_name desc, t2.report_date asc
end
else
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     group by t1.deal_name, t2.report_date
     order by t1.deal_name asc, t2.report_date asc
end
end


Commented:
For efficiency reasons is much better to use @tables and NOT #tables.
With this construction yoy have not control if tmp table exists or not.

Start with declaration...

DECLARE @temp1 TABLE (......)


INSERT INTO @temp1  SELECT .....

Author

Commented:
racek,
I can't do this. My users will not have permission to do a drop table

Commented:
Read my answer - my recommandation is another.

Your construction does not work - try it>
If 1 > 2
   select * into #tmpx from yourtable
Else
   select * into #tmpx from yourtable

My recommendation
DECLARE @tmpx TABLE (id int, .....)

insert into @tmpx (column list)
   SELECT column list from your tabe

Commented:
It is not necessary to drop @tables. @table will be cancelled when procedure or function is finnished.

Author

Commented:
Sorry, racek, didn;'t quite understand your logic

1. 1 > 2 is not my condition. My condiotion would be passed from front end program and based on that I will do query from differnet tables. Not the first SQL uses bbtsposition tabel and the second one from swap_info table

2. When you say @declare and insert into @tmpx, doe sit implies temp table and that will be destroyed once the session is finished ?
Top Expert 2012

Commented:
If you have SQL Server 2000 you can use a variable of type table as recek suggests, otherwise as a workaround drop tha table BEFORE using it as Scott has suggested:
IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
   DROP TABLE #temp1

This last should only be necessary once, but there is no harm leaving it.

Anthony

Author

Commented:
Ok got you. Would you please explain my first question then ?
that says
>>1. 1 > 2 is not my condition. My condiotion would be passed from front end program and based on that I will do query from differnet tables. Not the first SQL uses bbtsposition tabel and the second one from swap_info table

Author

Commented:
Anthony,
I am using SQL 7.0
Also used drop table, but the results are same

Author

Commented:
It is more of a condition problem than anything else. The procedure is trying to create the same #temp1 twice
Top Expert 2012

Commented:
Right, so ensure that the table does not exist before creating it with Select Into.

Anthony

Author

Commented:
No luck Anthony. I just added drop table with the condition, but comes back with same error

CREATE PROCEDURE up_Tickler_Report5
@clauseis varchar(20)=null,
@srptmonth datetime,
@alpha varchar(5)='A',
@sort varchar(5)='Asc'
AS

set nocount on
--if exists (select *
  --            from tempdb.dbo.sysobjects
    --          where id = object_id('tempdb.dbo.#temp1'))
--drop table #temp1

if @clauseis <> 'Swap'
begin
IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
   DROP TABLE #temp1

     SELECT d.deal_mid, d.deal_name, max(issuedate) AS issuedate
     INTO #temp1
     FROM bbtsposition b, Deal_Master d
     WHERE d.deal_mid=b.deal_mid and 1 = 0
     --load the #temp1 table
     INSERT INTO #temp1
     select d.deal_mid, d.deal_name, max(issuedate) as issuedate
     from bbtsposition b, Deal_Master d
     where d.deal_mid=b.deal_mid and
     issuedate is not null and
     upper(b.sec_type)=@clauseis  AND
     datediff(month,issuedate,@srptmonth) >= 0
      group by d.deal_mid,d.deal_name
end
else
begin
IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
   DROP TABLE #temp1

     select d.deal_mid, d.deal_name, max(b.trade_date) as issuedate
     into #temp1
     from swap_info b, Deal_Master d
     where d.deal_mid=b.deal_mid and 1=0
     Insert into #temp1
     select d.deal_mid, d.deal_name, max(b.trade_date) as issuedate
     from swap_info b, Deal_Master d
     where d.deal_mid=b.deal_mid and
     b.trade_date is not null and
     datediff(month,b.trade_date,@srptmonth) >= 0
     group by d.deal_mid,d.deal_name
end

select distinct deal_mid, Report_Date, PDF_received_date,comments
into #temp2
from deal_info
where datediff(month,report_date,@srptmonth) = 0
select max(h.deal_mid)as max_mid,max(m.Deal_name) as max_name,h.report_date,min(h.update_dt) as min_dt
into #temp3
from Deal_Info_change_history h,Deal_Master m
where m.Deal_MID =  h.deal_mid and
h.comments = 'Data Modified'
and datediff(month,h.report_date,@srptmonth) = 0
group by h.deal_mid,m.Deal_name,h.report_date
order by m.deal_name,h.report_date
if @alpha <> 'All'
begin
if @sort='desc'
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     and left(t1.deal_name,1) = @alpha
     group by t1.deal_name, t2.report_date
     order by t1.deal_name desc, t2.report_date asc
end
else
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     and left(t1.deal_name,1) = @alpha
     group by t1.deal_name, t2.report_date
     order by t1.deal_name asc, t2.report_date asc
end
end

else
begin
if @sort='desc'
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     group by t1.deal_name, t2.report_date
     order by t1.deal_name desc, t2.report_date asc
end
else
begin
     select distinct max(t1.deal_mid) as deal_mid,max(t1.deal_name) as deal_name,t2.report_date ,min(t2.PDF_received_date) as pdf_recd,min(t3.min_dt) as pdf_input,min(t2.comments) as comments
     from #temp1 t1 ,#temp2 t2 ,#temp3 t3
     where t1.deal_mid *= t2.deal_mid and
     t1.deal_mid *=t3.max_mid
     group by t1.deal_name, t2.report_date
     order by t1.deal_name asc, t2.report_date asc
end
end


Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I'm not sure the IF EXISTS logic as you've coded it actually works (for example, go into QA, create a temp table, populate it, then go into another session in QA, switch to tempdb, and list sysobjects; you won't see the temp table you just created).  The shortest, cleanest method that I've actually used and know that works is the one I posted earlier.  Also, again, it is possible that another procedure run by the same user before running this proc also created a temp table of #temp1.  The safest way is just to check first and delete the table if it exists.  For example:

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
   DROP TABLE #temp1
if @clauseis <> 'Swap'
begin
    SELECT d.deal_mid, d.deal_name, max(issuedate) AS issuedate
    INTO #temp1
    FROM bbtsposition b, Deal_Master d
    WHERE d.deal_mid=b.deal_mid and 1 = 0
    --load the #temp1 table
    INSERT INTO #temp1
    select d.deal_mid, d.deal_name, max(issuedate) as issuedate
    from bbtsposition b, Deal_Master d
    where d.deal_mid=b.deal_mid and
    issuedate is not null and
    upper(b.sec_type)=@clauseis  AND
    datediff(month,issuedate,@srptmonth) >= 0
     group by d.deal_mid,d.deal_name
end
else
...

Commented:
1 > 2 means - it does not matter what kind od condition you have. It simply cannot work because compiler (or what you want to call it) suposes, that you are creating #tmp table twice.

Construction like
IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
  DROP TABLE #temp1

is not necessary and is less efficient.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Did the error message refer to #temp2 or #temp1?  You may still have the issue with the other temps because you haven't added the drops for them yet.

Commented:
1 > 2 means - it does not matter what kind od condition you have. It simply cannot work because compiler (or what you want to call it) suposes, that you are creating #tmp table twice.

Construction like
IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
  DROP TABLE #temp1

is not necessary and is less efficient.

Author

Commented:
Scott,
If you notice my latest code us exactly same as suggested.But still getting the same error

Commented:
There is a bug asscociated with insert into and stored procedures:

http://support.microsoft.com/default.aspx?scid=166200

Try CREATE PROCEDURE up_Tickler_Report5 WITH RECOMPILE.

This is a shot in the dark, but worth a try.

Author

Commented:
racek,
You mean to say I can not have
if @clauseis <> 'Swap'
instead I should use 1 > 2
Does that mean SQLServer can not compare wit input vars ?
Does it have to be with a constants always ?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Racek's last point may be valid.  You can change your code to create the table only once like so:

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#table1', 'U') IS NOT NULL
    DROP TABLE #temp1
SELECT d.deal_mid, d.deal_name, MAX(issuedate) AS issuedate
INTO #temp1
FROM bbtsposition b, Deal_Master d
WHERE d.deal_mid=b.deal_mid AND 1 = 0
IF @clauseis <> 'SWAP'
BEGIN
    INSERT INTO #temp1
    select d.deal_mid, d.deal_name, max(issuedate) as issuedate
    from bbtsposition b, Deal_Master d
    where d.deal_mid=b.deal_mid and
    issuedate is not null and
    upper(b.sec_type)=@clauseis  AND
    datediff(month,issuedate,@srptmonth) >= 0
     group by d.deal_mid,d.deal_name
END --IF
ELSE ...
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
That is, pull the SELECT INTO ... that creates the table out of the conditional code and run it only once first.  The same for #temp1, #temp2 and #temp3.

I don't think there's a more efficient method to check for an existing table than the built-in OBJECT_ID function.

Author

Commented:
Scott,
It is always with #temp1.
I did not have any conditions before with #temp1. It was same like #temp2 and #temp3. But just now I added and gettign this

Commented:
by the way.

IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
 DROP TABLE #temp1
does not work here try this and you will get the same error:
-----------------------------
create table a (id int)
insert into a (id) values (1)
If 1 = 1
  begin
  IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
      DROP TABLE #temp1
  select * into #tmp1 from a
  end
Else
  begin
  IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
      DROP TABLE #temp1
  select * into #tmp1 from a
  end


Commented:
by the way.

IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
 DROP TABLE #temp1
does not work here try this and you will get the same error:
-----------------------------
create table a (id int)
insert into a (id) values (1)
If 1 = 1
  begin
  IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
      DROP TABLE #temp1
  select * into #tmp1 from a
  end
Else
  begin
  IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
      DROP TABLE #temp1
  select * into #tmp1 from a
  end


Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You should scratch the table yourself first before running the SP.  Issue a "DROP TABLE #temp1" from the session before running the SP.  Sometimes SQL gets "confused" because of a quirk in SQL code if the table already exists.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Racek, I don't think it's the IF that's failing.  Try this: (*) drop table #temp1 if it already exists; (*) remove the IF OBJECT_ID and DROPs; (*) run the code.  You will get the same error about the object already existing.  The problem is a "quirk" in SQL Server when you use SELECT INTO.

Commented:
Scott, try this job and you will see...

IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
     DROP TABLE #temp1

create table a (id int)
insert into a (id) values (1)
If 1 = 1
 begin
 IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
     DROP TABLE #temp1
 select * into #tmp1 from a
 end
Else
 begin
 IF OBJECT_ID('tempdb..#temp1', 'U') IS NOT NULL
     DROP TABLE #temp1
 select * into #tmp1 from a
 end


Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Change the code to pull the SELECT INTO first and only run it once.  The code below ran fine on a SQL 7.0 box:

create table a (id int)
insert into a (id) values (1)
select * into #tmp1 from a where 1 = 0
If 1 = 1
 begin
 insert into #tmp1 select * from a
 end
Else
 begin
 insert into #tmp1 select * from a
 end

Commented:
It is what i mean - The only way is to start with CREATE #table or DECLARE @table declaration before you start inserting into that. Construction with DROP TABLE ... doesn not work as Scott say>
.... Scott ...............................................
You should scratch the table yourself first before running the SP.  Issue a "DROP TABLE #temp1" from the session before running the SP.  Sometimes SQL gets "confused" because of a quirk in SQL code if the table already exists.
..........................................................
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
But it should work also if you have only one SELECT INTO rather than two (or more) in the SP.  If you issue the DROP without checking first you can get an error on the DROP because the table doesn't exist.  I have used the IF OBJECT_ID() approach for both temp and perm tables in literally dozens of SPs and it works fine.  What method do you suggest as better for testing whether or not a given table exists?



Author

Commented:
Guys,
How is it related to my stuff ?
How do I succesfully run teh SP. I am strill gettign teh same error with all the changes you asked for it.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
First, drop #temp1 using the connection that will compile the SP (this clean-up, if needed, is only for the connection doing the COMPILE; the run-time connection should work fine whether #temp1 exists or not).
Then try the SP below (note that the code below is just for #temp1; if it works, you can expand it for #temp2, etc.):

CREATE PROCEDURE up_Tickler_Report5
@clauseis varchar(20)=null,
@srptmonth datetime,
@alpha varchar(5)='A',
@sort varchar(5)='Asc'
AS

set nocount on
if object_id('tempdb..#temp1', 'u') is not null
    drop table #temp1
select d.deal_mid, d.deal_name, max(issuedate) as issuedate
into #temp1
from bbtsposition b, Deal_Master d
where      d.deal_mid=b.deal_mid and 1 = 0

if @clauseis <> 'Swap'
begin
    insert into #temp1
    select d.deal_mid, d.deal_name, max(issuedate)      
    from bbtsposition b, Deal_Master d
    where      d.deal_mid=b.deal_mid and
    issuedate is not null and
    upper(b.sec_type)=@clauseis  AND
    datediff(month,issuedate,@srptmonth) >= 0
    group by d.deal_mid,d.deal_name
end
else
begin
    insert into #temp1
    select d.deal_mid, d.deal_name, max(b.trade_date)
    from swap_info b, Deal_Master d
    where d.deal_mid=b.deal_mid and
    b.trade_date is not null and
    datediff(month,b.trade_date,@srptmonth) >= 0
    group by d.deal_mid,d.deal_name
end

Commented:
Did you tried my solution with tmp tables definition in the beginning of procedure?

Author

Commented:
I will test it and get back to you guys
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You can of course explicitly create temp tables yourself, as in:

CREATE TABLE #temp1 (
    deal_mid {datatype},
    deal_name {datatype},
    issuedate {datatype}
)

But if the datatypes/names in the SELECT change, it will be up to you to change the original create definition.  And you will still have to drop the table first if it exists because otherwise the create will fail.

Commented:
CREATE PROCEDURE up_Tickler_Report5
@clauseis varchar(20)=null,
@srptmonth datetime,
@alpha varchar(5)='A',
@sort varchar(5)='Asc'
AS

set nocount on

DECLARE @temp1 TABLE (
deal_mid int,
d.deal_name varchar(250),
trade_date datetime )

INSERT INTO @temp1 (deal_mid int, deal_name,trade_date )
select d.deal_mid, d.deal_name, max(issuedate) as issuedate
from bbtsposition b, Deal_Master d
where      d.deal_mid=b.deal_mid and 1 = 0

if @clauseis <> 'Swap'
begin
   INSERT INTO @temp1 (deal_mid int, deal_name,trade_date )select d.deal_mid, d.deal_name, max(issuedate)      
   from bbtsposition b, Deal_Master d
   where      d.deal_mid=b.deal_mid and
   issuedate is not null and
   upper(b.sec_type)=@clauseis  AND
   datediff(month,issuedate,@srptmonth) >= 0
   group by d.deal_mid,d.deal_name
end
else
begin
   insert into @temp1
   select d.deal_mid, d.deal_name, max(b.trade_date)
   from swap_info b, Deal_Master d
   where d.deal_mid=b.deal_mid and
   b.trade_date is not null and
   datediff(month,b.trade_date,@srptmonth) >= 0
   group by d.deal_mid,d.deal_name
end
Top Expert 2012

Commented:
racek,

jitganguly is using SQL Server 7 not SQL Server 2000

Anthony

Commented:
ok, look at first (my) comment. This is still the only working solution....

Author

Commented:
I would go with Scott's solution. Yes it is working now with no erros
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial