What's missing from my stored procedure?

[LeonelSanchezJr]

I am trying to get the following fields (column headings) and data into my report:

CUSTOMER_CODE HAWB# SHIPDATE ORIGIN DESTINATION
abc123 12345 05/01/2001 abc xyz
def456 67890 05/14/2001 def opq

IMAGES_MISSING STATUS
hawb pod
pod appt

Here is a copy of my stored procedure:

CREATE PROCEDURE SEL_MissingImages_RPT
@stationcode char(3) = null,
@custcode char(10) = null,
@fromshipdate datetime,
@toshipdate datetime,
@doctype char(18) = null
AS
set nocount on

create table #hawbnoimagerpt
(id1 int identity,
chawbnum char(10),
dshipdate datetime,
corigin char(3),
cdestin char(3),
cdoctype char(18),
chawbstat char(8) )

create table #podnoimagerpt
(id2 int identity,
chawbnum char(10),
dshipdate datetime,
corigin char(3),
cdestin char(3),
cdoctype char(18),
chawbstat char(8) )

select chawbnum, dshipdate, corigin, cdestin
into #hawboriginrpt
from hawb (nolock)
where corigin = @stationcode and
dshipdate between @fromshipdate and
@toshipdate and chawbnum not like 'ZZ%'

select distinct hawbdoctypes.chawbnum,
dshipdate, corigin, cdestin, cdoctype, chawbstat
into #imageoriginrpt
from #hawboriginrpt (nolock)
left join hawbdoctypes (nolock) on
#hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
left join status (nolock) on
#hawboriginrpt.chawbnum = status.chawbnum
where hawbdoctypes.cdoctype in ('HAWB','INLND_BL')

insert into #hawbnoimagerpt
select #hawboriginrpt.chawbnum,
#hawboriginrpt.dshipdate,
#hawboriginrpt.corigin,
#hawboriginrpt.cdestin,
cdoctype, chawbstat
from #hawboriginrpt (nolock)
left join hawbdoctypes (nolock) on
#hawboriginrpt.chawbnum = hawbdoctypes.chawbnum
where #hawboriginrpt.chawbnum not in (select
#imageoriginrpt.chawbnum from #imageoriginrpt
(nolock) )
order by #hawboriginrpt.chawbnum

drop table #hawboriginrpt
drop table #imageoriginrpt

select chawbnum, dshipdate, corigin, cdestin
into #hawbdestinrpt
from hawb (nolock) where cdestin = @stationcode
and dshipdate between @fromshipdate and
@toshipdate and chawbnum not like 'ZZ%'

select distinct hawbdoctypes.chawbnum, dshipdate, corigin,
cdestin, hawbdoctypes.cdoctype, chawbstat
into #imagedestinrpt
from #hawbdestinrpt (nolock)
left join hawbdoctypes (nolock) on
#hawbdestinrpt.chawbnum = hawbdoctypes.chawbnum
left join status (nolock) on
#hawbdestinrpt.chawbnum = status.chawbnum
where hawbdoctypes.cdoctype = 'POD'

insert into #podnoimagerpt
select #hawbdestinrpt.chawbnum, dshipdate, corigin,
cdestin, cdoctype, chawbstat
from #hawbdestinrpt (nolock)
left join hawbdoctypes (nolock) on
#hawbdestinrpt.chawbnum = hawbdoctypes.chawbnum
where #hawbdestinrpt.chawbnum not in (select
#imagedestinrpt.chawbnum from #imagedestinrpt
(nolock))
order by #hawbdestinrpt.chawbnum

drop table #hawbdestinrpt
drop table #imagedestinrpt

********************************************************
What's missing from my stored procedure? I get the results
that I want, but I am doing two "passes" to get my results
and then I get them in two different tables.

 

[ColinM]

You are using the SELECT INTO syntax which creates the table to store the data selected, when you already have used a create table statement.

 

[LeonelSanchezJr]

Well, I'm trying to get my results into ONE table. How can I do that?