varchar error

  • 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'

  • 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.

  • 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'.

  • 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)

  • 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.

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply