Hi,
I am getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
This is my code. What is wrong here?
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @.LadingType = 'inbound'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)If you supply the declares, that'd be a big help...|||Sorry, here are the declares:
CREATE PROCEDURE sp_InboundOutboundCSV
(
@.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'
DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'
DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'
DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'
DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'
DECLARE @.SQLStr varchar(8000)|||This compiles fine...it's something else...
DECLARE @.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'
DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'
DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'
DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'
DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'
DECLARE @.SQLStr varchar(8000)
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'|||Table compiles fine as well....
Post the whole sproc...
it's massive, isn't it.....|||Yes it is, but here t is... :) I have to submit it in parts since I can only post 1000 characters.
It does compile fine, but because I use EXEC a string I create it won't show you the error until you execute it.:
CREATE PROCEDURE sp_InboundOutboundCSV
(
@.startdate datetime,
@.enddate datetime,
@.Parent int,
@.Root int,
@.LadingType varchar(20)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.OrgLvl varchar(15)
SET @.OrgLvl = 'Shipper Number'
DECLARE @.ReportData varchar(50)
SET @.ReportData = 'tbl' + CAST(@.Root AS varchar) + 'ReportData'
DECLARE @.InvoiceData varchar(50)
SET @.InvoiceData = 'tbl' + CAST(@.Root AS varchar) + 'InvoiceData'
DECLARE @.ShipperData varchar(50)
SET @.ShipperData = 'tbl' + CAST(@.Root AS varchar) + 'ShipperData'
DECLARE @.AddrData varchar(50)
SET @.AddrData = 'tbl' + CAST(@.Root AS varchar) + 'AddrData'
DECLARE @.SQLStr varchar(8000)
IF @.LadingType ='' GOTO TotalsReport
IF @.LadingType <>'' GOTO LadingReport
LadingReport:
IF LOWER(@.LadingType) ='inbound' GOTO InboundReport
IF LOWER(@.LadingType) ='outbound' GOTO OutboundReport
OutboundReport:
BEGIN
SET @.SQLStr = 'SELECT ' + @.ReportData + '.InvoiceNum AS InvoiceNumber, ' +
@.ReportData + '.ShipperNum AS ShipperNumber, ' +
@.ReportData + '.InvoiceDate AS InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate AS ShipDate, ' +
@.ShipperData + '.ShipperName AS ShipperName, ' +
@.ShipperData + '.ShipperName2 AS ShipperCompName, ' +
@.ShipperData + '.ShipperAddr AS ShipperAddr, ' +
@.ShipperData + '.ShipperCity AS ShipperCity, ' +
@.ShipperData + '.ShipperState AS ShipperState, ' +
@.ShipperData + '.ShipperZip AS ShipperZip, ' +
@.ReportData + '.bName1 AS ConsigneeName, ' +
@.AddrData + '.bName2 AS ConsigneeCompName, ' +
@.AddrData + '.bAddr1 AS ConsigneeAddr, ' +
@.ReportData + '.bCity AS ConsigneeCity, ' +
@.ReportData + '.bState AS ConsigneeState, ' +
@.AddrData + '.bZip AS ConsigneeZip, ' +
@.AddrData + '.bCountry AS ConsigneeCountry, ' +
@.AddrData + '.bPhone AS ConsigneePhone, ' +
@.ReportData + '.TrackingNum AS TrackingNum, ' +
@.InvoiceData + '.CustRef1 AS RefNum1, ' +
@.InvoiceData + '.CustRef2 AS RefNum2, ' +
@.ReportData + '.UPSZone AS Zone, ' +
'tblLegendServiceLevel.ServiceLevel AS ServiceLevel, ' +
@.ReportData + '.Weight AS Weight, ' +
'tblLegendLading.Lading AS LadingDesc, ' +
'tblLegendSMPCodes.[Desc] AS SMPDesc, ' +
@.InvoiceData + '.GrossCharge AS GrossCharge, ' +
@.ReportData + '.Incentive AS Incentive, ' +
@.ReportData + '.NetCharge AS NetCharge, ' +
@.ReportData + '.AccessorialTotal AS AccessorialTotal, ' +
'tblCodeRef.[Desc] AS ComResDesc, ' +
@.InvoiceData + '.HundredWeight AS HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') '
EXEC (@.SQLStr)
END
GOTO Done|||InboundReport:
BEGIN
SET @.SQLStr = 'SELECT ' + @.ReportData + '.InvoiceNum AS InvoiceNumber, ' +
@.ReportData + '.ShipperNum AS ShipperNumber, ' +
@.ReportData + '.InvoiceDate AS InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate AS ShipDate, ' +
@.AddrData + '.aName1 AS ShipperName, ' +
@.AddrData + '.aName2 AS ShipperCompName, ' +
@.AddrData + '.aAddr1 AS ShipperAddr, ' +
@.AddrData + '.aCity AS ShipperCity, ' +
@.AddrData + '.aState AS ShipperState, ' +
@.AddrData + '.aZip AS ShipperZip, ' +
@.ReportData + '.bName1 AS ConsigneeName, ' +
@.AddrData + '.bName2 AS ConsigneeCompName, ' +
@.AddrData + '.bAddr1 AS ConsigneeAddr, ' +
@.ReportData + '.bCity AS ConsigneeCity, ' +
@.ReportData + '.bState AS ConsigneeState, ' +
@.AddrData + '.bZip AS ConsigneeZip, ' +
@.AddrData + '.bCountry AS ConsigneeCountry, ' +
@.AddrData + '.bPhone AS ConsigneePhone, ' +
@.ReportData + '.TrackingNum AS TrackingNum, ' +
@.InvoiceData + '.CustRef1 AS RefNum1, ' +
@.InvoiceData + '.CustRef2 AS RefNum2, ' +
@.ReportData + '.UPSZone AS Zone, ' +
'tblLegendServiceLevel.ServiceLevel AS ServiceLevel, ' +
@.ReportData + '.Weight AS Weight, ' +
'tblLegendLading.Lading AS LadingDesc, ' +
'tblLegendSMPCodes.[Desc] AS SMPDesc, ' +
@.InvoiceData + '.GrossCharge AS GrossCharge, ' +
@.ReportData + '.Incentive AS Incentive, ' +
@.ReportData + '.NetCharge AS NetCharge, ' +
@.ReportData + '.AccessorialTotal AS AccessorialTotal, ' +
'tblCodeRef.[Desc] AS ComResDesc, ' +
@.InvoiceData + '.HundredWeight AS HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') '
EXEC (@.SQLStr)
END
GOTO Done|||TotalsReport:
BEGIN
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @.LadingType = 'inbound'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.AddrData + '.aName1, ' +
@.AddrData + '.aName2, ' +
@.AddrData + '.aAddr1, ' +
@.AddrData + '.aCity, ' +
@.AddrData + '.aState, ' +
@.AddrData + '.aZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)
--Outbound
SET @.LadingType = 'outbound'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.ShipperData + '.ShipperName, ' +
@.ShipperData + '.ShipperName2, ' +
@.ShipperData + '.ShipperAddr, ' +
@.ShipperData + '.ShipperCity, ' +
@.ShipperData + '.ShipperState, ' +
@.ShipperData + '.ShipperZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)
--Misc
SET @.LadingType = 'misc'
SET @.SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @.ReportData + '.InvoiceNum, ' +
@.ReportData + '.ShipperNum, ' +
@.ReportData + '.InvoiceDate, ' +
@.InvoiceData + '.PickupTransDate, ' +
@.ShipperData + '.ShipperName, ' +
@.ShipperData + '.ShipperName2, ' +
@.ShipperData + '.ShipperAddr, ' +
@.ShipperData + '.ShipperCity, ' +
@.ShipperData + '.ShipperState, ' +
@.ShipperData + '.ShipperZip, ' +
@.ReportData + '.bName1, ' +
@.AddrData + '.bName2, ' +
@.AddrData + '.bAddr1, ' +
@.ReportData + '.bCity, ' +
@.ReportData + '.bState, ' +
@.AddrData + '.bZip AS, ' +
@.AddrData + '.bCountry, ' +
@.AddrData + '.bPhone, ' +
@.ReportData + '.TrackingNum, ' +
@.InvoiceData + '.CustRef1, ' +
@.InvoiceData + '.CustRef2, ' +
@.ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@.ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@.InvoiceData + '.GrossCharge, ' +
@.ReportData + '.Incentive, ' +
@.ReportData + '.NetCharge, ' +
@.ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@.InvoiceData + '.HundredWeight ' +
'FROM ' + @.ReportData +
' INNER JOIN ' + @.InvoiceData + ' ON ' + @.ReportData + '.DataID = ' + @.InvoiceData + '.DataID ' +
'INNER JOIN ' + @.AddrData + ' ON ' + @.ReportData + '.DataID = ' + @.AddrData + '.DataID ' +
'INNER JOIN ' + @.ShipperData + ' ON ' + @.ReportData + '.DataID = ' + @.ShipperData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @.ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @.ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @.ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @.InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @.ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE (' + @.ReportData + '.InvoiceDate BETWEEN ''' + CAST(@.startdate AS varchar) + ''' AND ''' + CAST(@.enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@.Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@.Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @.OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @.LadingType + ''')'
EXEC (@.SQLStr)
SELECT InvoiceNum AS InvoiceNumber,
ShipperNum AS ShipperNumber,
InvoiceDate AS InvoiceDate,
PickupTransDate AS ShipDate,
ShipperName AS ShipperName,
ShipperName2 AS ShipperCompName,
ShipperAddr AS ShipperAddr,
ShipperCity AS ShipperCity,
ShipperState AS ShipperState,
ShipperZip AS ShipperZip,
bName1 AS ConsigneeName,
bName2 AS ConsigneeCompName,
bAddr1 AS ConsigneeAddr,
bCity AS ConsigneeCity,
bState AS ConsigneeState,
bZip AS ConsigneeZip,
bCountry AS ConsigneeCountry,
bPhone AS ConsigneePhone,
TrackingNum AS TrackingNum,
CustRef1 AS RefNum1,
CustRef2 AS RefNum2,
UPSZone AS Zone,
ServiceLevel AS ServiceLevel,
Weight AS Weight,
Lading AS LadingDesc,
SMPCodeDesc AS SMPDesc,
GrossCharge AS GrossCharge,
Incentive AS Incentive,
NetCharge AS NetCharge,
AccessorialTotal AS AccessorialTotal,
CodeRefDesc AS ComResDesc,
HundredWeight AS HundredWeight
FROM #TotalsTemp
ORDER BY Lading
END
GOTO Done
Done:
END
GO|||So it's the execute that throws the error...
Instead of doing EXEC do SELECT @.SQLStr and take a look at it...can you post just that?
That'll be easier to debug...
Hey what's an extra 4 post counts...|||Well I get the error when I call the sproc. I don't think that the EXEC throws the error because this sproc worked fine with EXEC until I needed to make a chage on the bottom section starting with TotalsReport. so if you look at my original post it shows only that section and my second post shows all the declare's.
Thanks for your help.
P.S. the more posts the better :)|||What I'm suggesting is that the SQL statement is malformed...just putting the string together is fine...it's when you execute the sql that there's a problem (well, like duh brett)...
I was suggesting post what it buils...should be easier to see,,,wait...I can do that...|||YUP!
INSERT INTO #TotalsTemp SELECT tbl1ReportData.InvoiceNum, tbl1ReportData.ShipperNum, tbl1ReportData.InvoiceDate
, tbl1InvoiceData.PickupTransDate, tbl1AddrData.aName1, tbl1AddrData.aName2, tbl1AddrData.aAddr1
, tbl1AddrData.aCity, tbl1AddrData.aState, tbl1AddrData.aZip, tbl1ReportData.bName1, tbl1AddrData.bName2
, tbl1AddrData.bAddr1, tbl1ReportData.bCity, tbl1ReportData.bState, tbl1AddrData.bZip AS, tbl1AddrData.bCountry
, tbl1AddrData.bPhone, tbl1ReportData.TrackingNum, tbl1InvoiceData.CustRef1, tbl1InvoiceData.CustRef2
, tbl1ReportData.UPSZone, tblLegendServiceLevel.ServiceLevel, tbl1ReportData.Weight, tblLegendLading.Lading
, tblLegendSMPCodes.[Desc], tbl1InvoiceData.GrossCharge, tbl1ReportData.Incentive, tbl1ReportData.NetCharge
, tbl1ReportData.AccessorialTotal, tblCodeRef.[Desc], tbl1InvoiceData.HundredWeight
FROM tbl1ReportData INNER JOIN tbl1InvoiceData ON tbl1ReportData.DataID = tbl1InvoiceData.DataID
INNER JOIN tbl1AddrData ON tbl1ReportData.DataID = tbl1AddrData.DataID
INNER JOIN tblLegendServiceLevel ON tbl1ReportData.ServiceStandard = tblLegendServiceLevel.ServiceStandard
INNER JOIN tblLegendLading ON tbl1ReportData.LadingCode = tblLegendLading.LadingCode
INNER JOIN tblLegendSMPCodes ON tbl1ReportData.SMP2 = tblLegendSMPCodes.SMPCode
INNER JOIN tblCodeRef ON tbl1InvoiceData.ComRes = tblCodeRef.Code
INNER JOIN tblShipperNumberLookUp AS LookUp ON tbl1ReportData.ShipperNum = LookUp.ShipperNumber
INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID
INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child
INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl
WHERE (tbl1ReportData.InvoiceDate BETWEEN 'Mar 17 2004 12:00AM' AND 'Mar 17 2004 12:00AM')
AND (tblOrg_Unit_Hier.parent = 1) AND (tblOrg_lvls.Root = 1) AND (tblOrg_lvls.[Name] = 'Shipper Number')
AND (tblLegendLading.LadingType = 'Brett')|||WOW, I can't beleive I missed that. But with an sproc like this, it's bound to happend.
Thank you so much.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment