Update statement not working in SQL stored procedure

Question

  UPDATE rptMaster SET 
  nQtyOnHand  = (select (QTYONHND / rptMaster.UOMQTY) from itemMaster 
  where LOCNCODE = '001' and 
  itemMaster.ITEMNMBR = CASE 
  WHEN rptMaster.ITEMNMBR like '%P' 
  THEN SUBSTRING(rptMaster.ITEMNMBR, 1, DATALENGTH(rptMaster.ITEMNMBR) - 1) 
  ELSE rptMaster.ITEMNMBR
  END)

What's wrong with the above query. It is inside sql stored procedure. But I don't see the condition getting executed. I am checking if the itemnumber ends with 'P', then I want to ignore the 'P'. The table has records with itemnumber without ending in 'P' as well as ending in 'P'.

Earlier the query was something like below, which worked fine.

   UPDATE rptMaster SET 
   nQtyOnHand  = (select (QTYONHND / rptMaster.UOMQTY) from itemmaster 
   where LOCNCODE = '001' and 
   itemmaster.ITEMNMBR = rptMaster.ITEMNMBR)

I would bet on `LOCNCODE` being a NOT NULL column, and the subquery returns NULL — Yosi Dahari, Dec 28 '13 at 00:11

score 1 · Accepted Answer · answered Dec 28 '13 at 00:16

Your problem is most likely here;

SUBSTRING(rptMaster.ITEMNMBR, 1, DATALENGTH(rptMaster.ITEMNMBR) - 1)

DATALENGTH returns the strings length in bytes, while SUBSTRING takes a length in characters. What you want to use is not DATALENGTH, but LEN;

SUBSTRING(rptMaster.ITEMNMBR, 1, LEN(rptMaster.ITEMNMBR) - 1)

Update statement not working in SQL stored procedure

1 Answers1