SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


varchar error


varchar error

Author
Message
ry.rith
ry.rith
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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'




Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2627 Visits: 2490
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.
ry.rith
ry.rith
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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'.
Out For Justice
Out For Justice
SSC Eights!
SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)SSC Eights! (925 reputation)

Group: General Forum Members
Points: 925 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)
ry.rith
ry.rith
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116571 Visits: 41430
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? Wink



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15934 Visits: 18777
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? Wink



"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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116571 Visits: 41430
Matt Miller (10/31/2008)
"Must look Eye"


Heh... yeah... I forgot to add that on... Tongue So many cars... so little wax. Hehe

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ry.rith
ry.rith
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116571 Visits: 41430
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search