EXEC Strangeness

Hi all,

I would be very grateful if anyone can detect the problem with the following code. I have indicated the offending line. Basically if I hardcode in @.BelegId, it runs with no problems.

Declare @.SQL VarChar(1000)
Declare @.TableName VarChar(1000)
Declare @.Result Varchar(50)
Declare @.BelegId int
Set @.BelegId=447
Set @.ObjName='s_jps.ap_gr'

if (object_id (N'tempdb..#tempt') is not null) drop table #tempt;
create table #TempT (result varchar(1000))
--SELECT @.SQL='DECLARE @.RESULT VARCHAR(1000) '

SELECT @.SQL = 'SELECT CONVERT(VARCHAR(1000),' + @.ObjName + ') FROM '
SELECT @.SQL = @.SQL + 's_jps '
***PROBLEMATIC LINE FOLLOWS: IF I HARDCODE BELEGID, NO PROBLEMS***
SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + @.BelegId
SELECT @.sql = @.sql + ' and s_se_art_id=5'
INSERT #TempT Exec ( @.SQL )
SELECT * FROM #TempT
GO

He's a quick fix without knowing anything else about your environment...

Change Declare @.BelegId int to Declare @.BelegId varchar(5)

SQLDiva

|||SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + convert(varchar(10), @.BelegId)|||Please take a look at sp_executesql. It is safer to use sp_executesql than concatenating strings to execute in dynamic SQL. You need to protect the code against SQL injection attacks. And you should avoid dynamic SQL in this case if it is easier to just use a static SELECT statement specifying the table name. You probably don't need a temporary table also unless you are doing some complex processing.|||Thanks for the advice. I already looked into sp_executesql but had all sorts of problems with output parameters.

And I'm afraid that the unfortunate truth is that I do have to do some complex processing.

Exec Stored Procedure from command line?

Is it possible to execute a stored proc form the DOS command Line?Hi,
Yes , you could use the command line utility OSQL or ISQL to do execute a
procedure / tSQL from command line.
OSQL -Usa -Sserver -Ppassword -Qmaster..sp_help -oc:\result.txt'
Thanks
Hari
MCDBA
"Steve" <streesh@.bellsouth.net> wrote in message
news:zJ_0d.115942$0o5.2550@.bignews1.bellsouth.net...
> Is it possible to execute a stored proc form the DOS command Line?
>sql

Exec Stored Procedure from command line?

Is it possible to execute a stored proc form the DOS command Line?
Hi,
Yes , you could use the command line utility OSQL or ISQL to do execute a
procedure / tSQL from command line.
OSQL -Usa -Sserver -Ppassword -Qmaster..sp_help -oc:\result.txt'
Thanks
Hari
MCDBA
"Steve" <streesh@.bellsouth.net> wrote in message
news:zJ_0d.115942$0o5.2550@.bignews1.bellsouth.net. ..
> Is it possible to execute a stored proc form the DOS command Line?
>

EXEC stored procedure for every line of SELECT result table - how?

Hello,

Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.

I guess it's possible with cursors, but maybe it's possible to make it
easier.

Give an example, please.

Thank you in advance.

Hubert<hubert.trzewik@.gmail.com> wrote in message
news:1110986308.176019.292290@.l41g2000cwc.googlegr oups.com...
> Hello,
> Is it possible to EXEC stored procedure from a query?
> I want to execute stored procedure for every line of SELECT result
> table.
> I guess it's possible with cursors, but maybe it's possible to make it
> easier.
> Give an example, please.
> Thank you in advance.
> Hubert

A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:

http://www.sommarskog.se/share_data.html

After that, you can do something like this:

declare @.c1 int, @.c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()

open cur
fetch next from cur into @.c1, @.c2...
while @.@.fetch_status = 0
begin
exec dbo.MyProc @.c1, @.c2, ...
fetch next from cur into @.c1, @.c2...
end

close cur
deallocate cur

Simon|||Thanks. It was quite fast and easy to create such procedure.

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.