Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

varchar error Expand / Collapse
Author
Message
Posted Tuesday, October 28, 2008 8:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 10:33 PM
Points: 6, Visits: 23
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'



Post #593340
Posted Tuesday, October 28, 2008 8:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #593342
Posted Tuesday, October 28, 2008 8:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 10:33 PM
Points: 6, Visits: 23
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='SELECT dbo.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+' FROM dbo.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:

SELECT dbo.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
FROM dbo.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'.
Post #593344
Posted Wednesday, October 29, 2008 4:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 20, 2010 10:09 AM
Points: 803, Visits: 24
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)
Post #593463
Posted Friday, October 31, 2008 7:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 10:33 PM
Points: 6, Visits: 23
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.
Post #595355
Posted Friday, October 31, 2008 8:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:38 PM
Points: 35,353, Visits: 31,893
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  nvarchar(32))

Ask yourself... what do you get when you concatenate a VARCHAR(8000) with an NVARCHAR of any length? ;)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #595368
Posted Friday, October 31, 2008 8:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 7,105, Visits: 15,441
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  nvarchar(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?
Post #595373
Posted Friday, October 31, 2008 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:38 PM
Points: 35,353, Visits: 31,893
Matt Miller (10/31/2008)
"Must look Eye"


Heh... yeah... I forgot to add that on... :P So many cars... so little wax.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #595378
Posted Friday, October 31, 2008 10:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 10:33 PM
Points: 6, Visits: 23
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
Post #595392
Posted Friday, October 31, 2008 10:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:38 PM
Points: 35,353, Visits: 31,893
You don't understand... it IS the problem because it causes @SQL to be truncated as if IT were an NVARCHAR.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #595396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse