Results 1 to 13 of 13
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: EXEC (@SQLString) Problem.

    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)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you supply the declares, that'd be a big help....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    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)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This compiles fine....it's something else....

    Code:
    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 + ''')'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Table compiles fine as well.....

    Post the whole sproc...


    it's massive, isn't it......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    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

  7. #7
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    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

  8. #8
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    YUP!

    Code:
    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')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    WOW, I can't beleive I missed that. But with an sproc like this, it's bound to happend.

    Thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •