October 28, 2008 at 8:41 pm
I'm a T-SQL basic for about 1 year. now i am solving for crosstab report, so i try to use cursor with the long string of varchar to generate dynamic Select statement. Logical Error occurred while the inner string was corrupted abnormally, for example i write
Declare @sql=' select xxxxxxxxxxxxxxxxx'
set @sql=@sql+' From T1 inner join table2 where wwwwwwwwwwwww'
October 28, 2008 at 8:50 pm
What sticks out to me is you did not name the data type of @SQL when you declared it.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 28, 2008 at 8:56 pm
oh, no it's just brief explanation so you can see detail below.
Alter Proc rsp_IC_PackingList_CrossTab (@po_no nvarchar(32))
AS
BEGIN
DECLARE cShips CURSOR FOR
SELECT ShipmentDate,Id From tab_ic_packing_list_shipment
WHERE PackingListNo=@po_no
ORDER BY 1
Declare @sql as varchar( 8000)
Set @sql='SELECTdbo.tab_IC_Packing_List_Detail.ItemCode,
(SELECT Description
FROM dbo.tab_ic_itemcode
WHERE dbo.tab_Ic_itemcode.itemcode = dbo.tab_IC_Packing_List_Detail.ItemCode) AS Description,
dbo.tab_IC_Packing_List_Detail.SizeGroupID,
dbo.tab_IC_Packing_List_Detail.Cost,
dbo.tab_IC_Packing_List_Detail.Qty,
dbo.tab_IC_Packing_List_Detail.Discount,
dbo.tab_IC_Packing_List_Detail.Qty * Cost * (1-Discount) as GrantTotal,
dbo.tab_IC_Packing_List_Detail.FOC,
dbo.tab_IC_Packing_List_Detail.Qty + dbo.tab_IC_Packing_List_Detail.FOC as PoFOC,
dbo.tab_IC_Packing_List_Detail.CreateDate,' + char(13)
--WHERE state = ORDER BY 1
DECLARE @ShipmentDate DateTime
Declare @id as varchar(5000)
OPEN cShips
FETCH NEXT FROM cShips
INTO @ShipmentDate,@id
Declare @i as int
Set @i=0
WHILE @@FETCH_STATUS = 0
BEGIN
set @i=@i+1
Set @sql=@sql + ' SUM(CASE ShipmentId WHEN '+ char(39)+@id+char(39)+' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty'+Cast(@i as varchar) +','+char(13)
Set @sql=@sql+' SUM(CASE ShipmentId WHEN '+ char(39)+@id+char(39)+' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc'+Cast(@i as varchar) +','+char(13)
Set @sql=@sql+' SUM(CASE ShipmentId WHEN '+ char(39)+@id+char(39)+' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt'+Cast(@i as varchar) +','+char(13)
FETCH NEXT FROM cShips
INTO @ShipmentDate,@id
END
Close cShips
Deallocate cShips
Set @sql=@sql+ '(select Sum(QtyInMainUnit)
FROM tab_IC_Item_In_Location
WHERE
tab_IC_Item_In_Location.ItemCode=dbo.tab_IC_Packing_List_Detail.ItemCode AND
tab_IC_Item_In_Location.SizeGroupID=dbo.tab_IC_Packing_List_Detail.SizeGroupID) As StockBalance,
(Select sum(QtyInMainUnit)/9
From tab_SO_Invoice_Summary
WHERE
tab_SO_Invoice_Summary.Item=tab_IC_Packing_List_Detail.ItemCode And
tab_SO_Invoice_Summary.SizeGroupId=tab_IC_Packing_List_Detail.SizeGroupId And
tab_SO_Invoice_Summary.CreateDate Between DateAdd(m,-9,tab_IC_Packing_List_Detail.CreateDate)AND tab_IC_Packing_List_Detail.CreateDate) As Avg9Month,
tab_IC_Packing_List_Detail.Remark ' + char(13)
Set @sql=@sql+' FROMdbo.tab_IC_Packing_List_Detail INNER JOIN dbo.tab_IC_Packing_List_Shipment_Detail
ON dbo.tab_IC_Packing_List_Detail.TransID = dbo.tab_IC_Packing_List_Shipment_Detail.PODetailID' + char(13)
Set @sql=@sql+' WHERE dbo.tab_IC_Packing_List_Detail.PackingListNo='+ char(39)+ @po_no + char(39) + char(13)
Set @sql=@sql+'GROUP BY
dbo.tab_IC_Packing_List_Detail.ItemCode,
dbo.tab_IC_Packing_List_Detail.SizeGroupID,
dbo.tab_IC_Packing_List_Detail.Cost,
dbo.tab_IC_Packing_List_Detail.Qty,
dbo.tab_IC_Packing_List_Detail.Discount,
dbo.tab_IC_Packing_List_Detail.FOC,
dbo.tab_IC_Packing_List_Detail.CreateDate,
dbo.tab_IC_Packing_List_Detail.Remark'
print(@sql)
exec (@sql)
END
GO
OUTPUT:
SELECTdbo.tab_IC_Packing_List_Detail.ItemCode,
(SELECT Description
FROM dbo.tab_ic_itemcode
WHERE dbo.tab_Ic_itemcode.itemcode = dbo.tab_IC_Packing_List_Detail.ItemCode) AS Description,
dbo.tab_IC_Packing_List_Detail.SizeGroupID,
dbo.tab_IC_Packing_List_Detail.Cost,
dbo.tab_IC_Packing_List_Detail.Qty,
dbo.tab_IC_Packing_List_Detail.Discount,
dbo.tab_IC_Packing_List_Detail.Qty * Cost * (1-Discount) as GrantTotal,
dbo.tab_IC_Packing_List_Detail.FOC,
dbo.tab_IC_Packing_List_Detail.Qty + dbo.tab_IC_Packing_List_Detail.FOC as PoFOC,
dbo.tab_IC_Packing_List_Detail.CreateDate,
SUM(CASE ShipmentId WHEN '301CB3A5-382E-486D-9074-A35304A1A99B' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty1,
SUM(CASE ShipmentId WHEN '301CB3A5-382E-486D-9074-A35304A1A99B' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc1,
SUM(CASE ShipmentId WHEN '301CB3A5-382E-486D-9074-A35304A1A99B' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt1,
SUM(CASE ShipmentId WHEN 'F66634BC-9709-4F0E-961B-2B01C7A97943' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty2,
SUM(CASE ShipmentId WHEN 'F66634BC-9709-4F0E-961B-2B01C7A97943' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc2,
SUM(CASE ShipmentId WHEN 'F66634BC-9709-4F0E-961B-2B01C7A97943' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt2,
SUM(CASE ShipmentId WHEN '31796E84-5C81-450E-AB1F-1CA6BCB75473' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty3,
SUM(CASE ShipmentId WHEN '31796E84-5C81-450E-AB1F-1CA6BCB75473' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc3,
SUM(CASE ShipmentId WHEN '31796E84-5C81-450E-AB1F-1CA6BCB75473' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt3,
SUM(CASE ShipmentId WHEN '25F67ED1-0151-4AA9-833B-E2C749852BE0' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty4,
SUM(CASE ShipmentId WHEN '25F67ED1-0151-4AA9-833B-E2C749852BE0' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc4,
SUM(CASE ShipmentId WHEN '25F67ED1-0151-4AA9-833B-E2C749852BE0' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt4,
SUM(CASE ShipmentId WHEN '61CE059D-04CF-455E-A091-D09961DBA8C3' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty5,
SUM(CASE ShipmentId WHEN '61CE059D-04CF-455E-A091-D09961DBA8C3' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc5,
SUM(CASE ShipmentId WHEN '61CE059D-04CF-455E-A091-D09961DBA8C3' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt5,
SUM(CASE ShipmentId WHEN 'AAC92855-89B2-41A5-B9D8-D84D47499D1D' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty6,
SUM(CASE ShipmentId WHEN 'AAC92855-89B2-41A5-B9D8-D84D47499D1D' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc6,
SUM(CASE ShipmentId WHEN 'AAC92855-89B2-41A5-B9D8-D84D47499D1D' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt6,
(select Sum(QtyInMainUnit)
FROM tab_IC_Item_In_Location
WHERE
tab_IC_Item_In_Location.ItemCode=dbo.tab_IC_Packing_List_Detail.ItemCode AND
tab_IC_Item_In_Location.SizeGroupID=dbo.tab_IC_Packing_List_Detail.SizeGroupID) As StockBalance,
(Select sum(QtyInMainUnit)/9
From tab_SO_Invoice_Summary
WHERE
tab_SO_Invoice_Summary.Item=tab_IC_Packing_List_Detail.ItemCode And
tab_SO_Invoice_Summary.SizeGroupId=tab_IC_Packing_List_Detail.SizeGroupId And
tab_SO_Invoice_Summary.CreateDate Between DateAdd(m,-9,tab_IC_Packing_List_Detail.CreateDate)AND tab_IC_Packing_List_Detail.CreateDate) As Avg9Month,
tab_IC_Packing_List_Detail.Remark
FROMdbo.tab_IC_Packing_List_Detail INNER JOIN
dbGROUP BY
dbo.tab_IC_Packing_List_Detail.ItemCode,
dbo.tab_IC_Packing_List_Detail.SizeGroupID,
dbo.tab_IC_Packing_List_Detail.Cost,
dbo.tab_IC_Packing_List_Detail.Qty,
dbo.tab_IC_Packing_List_Detail.Discount,
dbo.tab_IC_Packing_List_Detail.FOC,
dbo.tab_IC_Packing_List_Detail.CreateDate,
dbo.tab_IC_Packing_List_Detail.Remark
Server: Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'BY'.
October 29, 2008 at 4:37 am
You could try splitting it into 2 or more bits of sql and concatenating them at the end. Also try using nvarchar instead of varchar (I'm not sure why it would make a difference but it might be worth a try)
declare @Sql1 nvarchar(4000)
declare @Sql2 nvarchar(4000)
SET @Sql1 = 'SELECT .....'
SET @Sql2 = 'FROM ...'
exec (@Sql1 + ' ' + @Sql2)
October 31, 2008 at 7:25 pm
Thank so much,
that you tell me about the solution.
I also do the same of yours to complete the crosstab-report, but something that i want is to know why it's process abnormally.
any technique, please inform me.
your faithfully.
October 31, 2008 at 8:22 pm
ry.rith (10/31/2008)
Thank so much,that you tell me about the solution.
I also do the same of yours to complete the crosstab-report, but something that i want is to know why it's process abnormally.
any technique, please inform me.
your faithfully.
Observe the following line of your code...
Set @sql=@sql+' WHERE dbo.tab_IC_Packing_List_Detail.PackingListNo='+ char(39)+ @po_no + char(39) + char(13)
Looks harmless enough until you trace it back to the real problem... which is...
Alter Proc rsp_IC_PackingList_CrossTab (@po_no [font="Arial Black"]nvarchar[/font](32))
Ask yourself... what do you get when you concatenate a VARCHAR(8000) with an NVARCHAR of any length? ;)
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2008 at 8:45 pm
Jeff Moden (10/31/2008)
ry.rith (10/31/2008)
Thank so much,that you tell me about the solution.
I also do the same of yours to complete the crosstab-report, but something that i want is to know why it's process abnormally.
any technique, please inform me.
your faithfully.
Observe the following line of your code...
Set @sql=@sql+' WHERE dbo.tab_IC_Packing_List_Detail.PackingListNo='+ char(39)+ @po_no + char(39) + char(13)Looks harmless enough until you trace it back to the real problem... which is...
Alter Proc rsp_IC_PackingList_CrossTab (@po_no [font="Arial Black"]nvarchar[/font](32))Ask yourself... what do you get when you concatenate a VARCHAR(8000) with an NVARCHAR of any length? ;)
"Must look Eye"
or
"A DBA has GOT to know his limitations" (with your best dirty Harry impersonation).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 31, 2008 at 9:09 pm
Matt Miller (10/31/2008)
"Must look Eye"
Heh... yeah... I forgot to add that on... 😛 So many cars... so little wax. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2008 at 10:29 pm
oh, it's my bit mistake that i use nvarchar to cocatenate with varchar variable. but it's not a problem because some value that loose is not in the WHERE clause but FROM clause(after INNER Join).
why like this?
thank somuch
October 31, 2008 at 10:37 pm
You don't understand... it IS the problem because it causes @SQL to be truncated as if IT were an NVARCHAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply