asked on

MS SQL -2005 Procedure

I am using MS SQL-2005, & created the following mentioned procedure which is working properly
Use DLI_DB
SELECT
      File_job=left(z.file_job,5),
      z.transporter,
      TransportationCost=Sum(Z.transportationcost),
      LocalTransportation=sum(z.localtransportcost),
      Commission=sum(z.commission),
      BorderExp=sum(z.TaxPaid),
      Extrapayment=sum(extrapayment),
      CDDeductFromTransporter=sum(z.CDDeductFromTransporter),
      CDWaiveToTransporter=sum(z.CDWaiveToTransporter),
      DeductionFromTransporter=sum(z.DeductionFromTransporter),
      Debit=isnull(z.debit,0),
      Dues=sum(z.Dues),
      Balance=(sum(z.localtransportcost)+sum(z.TransportationCost)+sum(extrapayment)+sum(z.Com
mission)+sum(z.taxpaid)+sum(z.CDWaiveToTransporter)
      (sum(z.CDDeductFromTransporter)+sum(z.DeductionFromTransporter)),
      DateOut=count(b.date_out),
      DateIn=count(b.date_in)
FROM cargodetailcontainertransportdet z join cargodetailcontainer b on b.sn=z.sn
WHERE z.TRANSPORTER='Tayaba Trading & Logistic Company'
group by left(z.file_job,5),
z.debit, z.transporter

Problem
-----------
When I am converting above statement after adding following line in procedure and running from front end it is showing error.
Create Procedure [dbo].[AT_RptTransporterPaymentSummary] (@Transporter as varchar(100)) as

Is there any need to define procedure with database name or where I am doing mistake please help.
Rgds.
Mehram

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mehram

ASKER

YES I HAVE REMOVED IT.
I THINK PROBLEM IS THAT:
I AM USING TWO STATEMENT IN ONE PROCEDURE THE FIRST STATEMENT IS CREATING TEMP2 TABLE AND IN SECOND STATEMENT I AM JOINING TEMP2 TABLE AND INSERTING DATA INTO THIS.
PRODCEDURE:
ALTER Procedure [dbo].[AT_RptTransporterPaymentSummary] (@Transporter as varchar(100)) as

if object_id('temp2') is not null Drop Table Temp2

begin
SELECT
      File_job=left(z.file_job,5),
      z.transporter,
      TransportationCost=Sum(Z.transportationcost),
      LocalTransportation=sum(z.localtransportcost),
      Commission=sum(z.commission),
      BorderExp=sum(z.TaxPaid),
      Extrapayment=sum(extrapayment),
      CDDeductFromTransporter=sum(z.CDDeductFromTransporter),
      CDWaiveToTransporter=sum(z.CDWaiveToTransporter),
      DeductionFromTransporter=sum(z.DeductionFromTransporter),
      Debit=isnull(z.debit,0),
      Dues=sum(z.Dues),
Balance=(sum(z.localtransportcost)+sum(z.TransportationCost)+sum(extrapayment)+sum(z.Commission)+sum(z.taxpaid)+sum(z.CDWaiveToTransporter)
)-(sum(z.CDDeductFromTransporter)+sum(z.DeductionFromTransporter)),
      DateOut=count(b.date_out),
      DateIn=count(b.date_in)
into temp2
FROM cargodetailcontainertransportdet z join cargodetailcontainer b on b.sn=z.sn
WHERE z.TRANSPORTER=@transporter
group by left(z.file_job,5), z.debit, z.transporter
end

Begin
update a
set a.debit=x.amount
from
(select headsub,Amount=sum(amount) from CargoDetailVendorPayment where headsub=@transporter and vd='0' group by headsub)x
join temp2 a on a.transporter=x.headsub
end

SQL execute error:
Msg 207, Level 16, State 3, Procedure AT_RptTransporterPaymentSummary, Line 55
Invalid column name 'transporter'.
Msg 207, Level 16, State 1, Procedure AT_RptTransporterPaymentSummary, Line 55
Invalid column name 'debit'.