I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
in particular, the code i am examining is
sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?I would wager that SurveyDelete is the name of a stored procedure in the database. Have a look there.|||i bet your right. thanks.
Showing posts with label syntax. Show all posts
Showing posts with label syntax. Show all posts
Tuesday, March 27, 2012
Friday, March 23, 2012
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)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.
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.
Wednesday, March 21, 2012
Exclude Words
I have built a functioning full text search tool on my companies
extranet site. I use the containstable function. I was curious what
the syntax or process would be to do a "without these words" box to
allow users to filter their search results better.
Thanks,
Daniel Hirsch
Daniel,
You can use the NOT keyword in either your CONTAINS or CONTAINSTABLE query,
however, there are some restrictions on how it can be used, from the BOL
title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains
search conditions.
When <contains_search_condition> contains parenthesized groups, these
parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using these
logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not
allowed.
NOT cannot be specified before the first term (for example, CONTAINS
(mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can
therefore be applied in any order.
Below are two examples CONTAINSTABLE:
use pubs
-- returns 2 rows when NOT, not included and 0 rows when NOT is included
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
-- another example of using Mutiple columns (same table) with a NOT
condition:
SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city,
KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY]
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dan Hirsch" <daniel.hirsch@.mckesson.com> wrote in message
news:1114200575.445065.76320@.f14g2000cwb.googlegro ups.com...
> I have built a functioning full text search tool on my companies
> extranet site. I use the containstable function. I was curious what
> the syntax or process would be to do a "without these words" box to
> allow users to filter their search results better.
> Thanks,
> Daniel Hirsch
>
|||Thanks Allot John. the NOT is exactly what I was looking for. I
figured there was syntax like that, but I wasn't sure exactly.
Thanks Again,
Daniel Hirsch
sql
extranet site. I use the containstable function. I was curious what
the syntax or process would be to do a "without these words" box to
allow users to filter their search results better.
Thanks,
Daniel Hirsch
Daniel,
You can use the NOT keyword in either your CONTAINS or CONTAINSTABLE query,
however, there are some restrictions on how it can be used, from the BOL
title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains
search conditions.
When <contains_search_condition> contains parenthesized groups, these
parenthesized groups are evaluated first.
After evaluating parenthesized groups, these rules apply when using these
logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not
allowed.
NOT cannot be specified before the first term (for example, CONTAINS
(mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can
therefore be applied in any order.
Below are two examples CONTAINSTABLE:
use pubs
-- returns 2 rows when NOT, not included and 0 rows when NOT is included
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
-- another example of using Mutiple columns (same table) with a NOT
condition:
SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city,
KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY]
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dan Hirsch" <daniel.hirsch@.mckesson.com> wrote in message
news:1114200575.445065.76320@.f14g2000cwb.googlegro ups.com...
> I have built a functioning full text search tool on my companies
> extranet site. I use the containstable function. I was curious what
> the syntax or process would be to do a "without these words" box to
> allow users to filter their search results better.
> Thanks,
> Daniel Hirsch
>
|||Thanks Allot John. the NOT is exactly what I was looking for. I
figured there was syntax like that, but I wasn't sure exactly.
Thanks Again,
Daniel Hirsch
sql
Wednesday, March 7, 2012
Exception when ExecuteNonQuery is executed
Hi All,
SqlComand.ExecuteNonQuery()
gives the following exception:
Incorrect syntax near '1.', .Net SqlClient Data Provider, at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
Would anybody be able to tell me the cause of this exception?
Thanks.This error means that the SQL statement you are trying to execute is
syntactically invalid. Try running the same SQL statement via Query
Analyzer for debugging. Post the CommandText if you need more help.
Hope this helps.
Dan Guzman
SQL Server MVP
"kd" <kd@.discussions.microsoft.com> wrote in message
news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
> Hi All,
> SqlComand.ExecuteNonQuery()
> gives the following exception:
> Incorrect syntax near '1.', .Net SqlClient Data Provider, at
> System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior,
> RunBehavior runBehavior, Boolean returnStream)
> Would anybody be able to tell me the cause of this exception?
> Thanks.|||Hi Dan,
The SQL command is being executed in the Query analyser and returns 0 rows.
Here is the SQL statement
select count(*) from testtab where UpdateDt = getdate()
Could there be any other reason for the exception?
Regards,
kd
"Dan Guzman" wrote:
> This error means that the SQL statement you are trying to execute is
> syntactically invalid. Try running the same SQL statement via Query
> Analyzer for debugging. Post the CommandText if you need more help.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
>
>|||kd wrote:
> Hi Dan,
> The SQL command is being executed in the Query analyser and returns 0
> rows. Here is the SQL statement
> select count(*) from testtab where UpdateDt = getdate()
You're not making any sense. This is a record-returning select statement. It
should always return a single record. In this case, the record will contain
a single field containing the value "0" if no rows in testtab meet the
criteria in the WHERE clause.
> Could there be any other reason for the exception?
Yes, you are using ExecuteNonQuery to execute a select statement that
returns records. Although I am curious: I do not see the character "1"
(mentioned in the error message) in this sql statement.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
I am sorry about that...its not the ExecuteNonQuery that is throwing an
exception, but, it is the ExecuteScalar!
kd
"Bob Barrows [MVP]" wrote:
> kd wrote:
> You're not making any sense. This is a record-returning select statement.
It
> should always return a single record. In this case, the record will contai
n
> a single field containing the value "0" if no rows in testtab meet the
> criteria in the WHERE clause.
>
> Yes, you are using ExecuteNonQuery to execute a select statement that
> returns records. Although I am curious: I do not see the character "1"
> (mentioned in the error message) in this sql statement.
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||So is your problem solved? If not, we're going to need to see some code
(although this would be better handled in one of the dotnet newsgroups)
Bob Barrows
kd wrote:
> Hi Bob,
> I am sorry about that...its not the ExecuteNonQuery that is throwing
> an exception, but, it is the ExecuteScalar!
> kd
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
The problem is solved. The following statement was the reason for the
exception to occur; though I can't understand why this statement cannot be
given.
SqlCommandObj.CommandText = CommandType.Text
When I commented the line, the exception did not occur when
SqlCommandObj.ExecuteScalar was executed.
Thanks for the assistance.
Regards,
kd.
"Bob Barrows [MVP]" wrote:
> So is your problem solved? If not, we're going to need to see some code
> (although this would be better handled in one of the dotnet newsgroups)
> Bob Barrows
> kd wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||kd wrote:
> Hi Bob,
> The problem is solved. The following statement was the reason for the
> exception to occur; though I can't understand why this statement
> cannot be given.
> SqlCommandObj.CommandText = CommandType.Text
What are you trying to do with that statement? CommandText is the text
for the command and CommandType is the type of command: SP, TableDirect,
or Text.
David Gugick
Imceda Software
www.imceda.com
SqlComand.ExecuteNonQuery()
gives the following exception:
Incorrect syntax near '1.', .Net SqlClient Data Provider, at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
Would anybody be able to tell me the cause of this exception?
Thanks.This error means that the SQL statement you are trying to execute is
syntactically invalid. Try running the same SQL statement via Query
Analyzer for debugging. Post the CommandText if you need more help.
Hope this helps.
Dan Guzman
SQL Server MVP
"kd" <kd@.discussions.microsoft.com> wrote in message
news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
> Hi All,
> SqlComand.ExecuteNonQuery()
> gives the following exception:
> Incorrect syntax near '1.', .Net SqlClient Data Provider, at
> System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior,
> RunBehavior runBehavior, Boolean returnStream)
> Would anybody be able to tell me the cause of this exception?
> Thanks.|||Hi Dan,
The SQL command is being executed in the Query analyser and returns 0 rows.
Here is the SQL statement
select count(*) from testtab where UpdateDt = getdate()
Could there be any other reason for the exception?
Regards,
kd
"Dan Guzman" wrote:
> This error means that the SQL statement you are trying to execute is
> syntactically invalid. Try running the same SQL statement via Query
> Analyzer for debugging. Post the CommandText if you need more help.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:D54DBDB4-2CBA-4AA9-A658-D154F47F69B1@.microsoft.com...
>
>|||kd wrote:
> Hi Dan,
> The SQL command is being executed in the Query analyser and returns 0
> rows. Here is the SQL statement
> select count(*) from testtab where UpdateDt = getdate()
You're not making any sense. This is a record-returning select statement. It
should always return a single record. In this case, the record will contain
a single field containing the value "0" if no rows in testtab meet the
criteria in the WHERE clause.
> Could there be any other reason for the exception?
Yes, you are using ExecuteNonQuery to execute a select statement that
returns records. Although I am curious: I do not see the character "1"
(mentioned in the error message) in this sql statement.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
I am sorry about that...its not the ExecuteNonQuery that is throwing an
exception, but, it is the ExecuteScalar!
kd
"Bob Barrows [MVP]" wrote:
> kd wrote:
> You're not making any sense. This is a record-returning select statement.
It
> should always return a single record. In this case, the record will contai
n
> a single field containing the value "0" if no rows in testtab meet the
> criteria in the WHERE clause.
>
> Yes, you are using ExecuteNonQuery to execute a select statement that
> returns records. Although I am curious: I do not see the character "1"
> (mentioned in the error message) in this sql statement.
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||So is your problem solved? If not, we're going to need to see some code
(although this would be better handled in one of the dotnet newsgroups)
Bob Barrows
kd wrote:
> Hi Bob,
> I am sorry about that...its not the ExecuteNonQuery that is throwing
> an exception, but, it is the ExecuteScalar!
> kd
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Hi Bob,
The problem is solved. The following statement was the reason for the
exception to occur; though I can't understand why this statement cannot be
given.
SqlCommandObj.CommandText = CommandType.Text
When I commented the line, the exception did not occur when
SqlCommandObj.ExecuteScalar was executed.
Thanks for the assistance.
Regards,
kd.
"Bob Barrows [MVP]" wrote:
> So is your problem solved? If not, we're going to need to see some code
> (although this would be better handled in one of the dotnet newsgroups)
> Bob Barrows
> kd wrote:
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||kd wrote:
> Hi Bob,
> The problem is solved. The following statement was the reason for the
> exception to occur; though I can't understand why this statement
> cannot be given.
> SqlCommandObj.CommandText = CommandType.Text
What are you trying to do with that statement? CommandText is the text
for the command and CommandType is the type of command: SP, TableDirect,
or Text.
David Gugick
Imceda Software
www.imceda.com
Sunday, February 19, 2012
EXcel sheet into database with stored procedure
Hi,
What is the syntax of storing the content of an Excelsheet in a predefined
table
with a stored prcedure.
Arno de Jong, The Netherlands.A.M. de Jong (arnojo@.wxs.nl) writes:
> What is the syntax of storing the content of an Excelsheet in a predefined
> table
> with a stored prcedure.
As I don't use Excel much with SQL Server, I went to http://www.google.com
and entered:
Excel INSERT "SQL Server"
and god a couple of hits. The first one is a blog entry from SQL Server
MVP Dejan Sarka:
http://solidqualitylearning.com/blo.../03/12/441.aspx.
This should get you started.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)