I have a stored procedure to get the data I want:
SELECT a.SONum, a.Seq1, a.SptNum, a.Qty1, a.SalUniPriP, a.PayNum, a.InvNum,
a.BLNum, c.ETD, c.ShpNum, f.IssBan
FROM OrdD a JOIN OrdH b ON a.SONum = b.SONum
LEFT JOIN Invh c ON a.InvNum = c.InvNum
LEFT JOIN cus d ON b.CusCod = d.CusCod
LEFT JOIN BL e ON a.BLNum = e.BLNum
LEFT JOIN PayMasH f ON f.PayNum = a.PayNum
LEFT JOIN Shipment g ON g.ShpNum = c.ShpNum
WHERE b.CusCod IN (SELECT CusCod FROM UsrInc WHERE UseID=@UserID and UseLev=@UserLvl)
AND d.CusGrp = @CusGrp
After I get those records into cursor, I used ROWLOCK, UPDLOCK to lock all the related invoice numbers.
SELECT InvNum FROM Invh WITH (ROWLOCK,UPDLOCK) WHERE InvNum =
Can I issue locking on the table INVH at the point I select the table from a few table using join command at my store procedure?
Any advice, please!
Yes, you can, provided you format your SQL correctly. The general pattern you require is as follows:
BEGIN TRAN
SELECT *
FROM table_1 AS t1 {WITH (UPDLOCK, ROWLOCK)}
JOIN table_2 AS t2 {WITH (UPDLOCK, ROWLOCK)}
ON t1.a = t2.a
COMMIT TRAN -- locking ends here
Where {} is optional (omit the {}). So lock as you put the "WITH (UPDLOCK, ROWLOCK)" clause on each table you wish to lock, it will work.
So you can put the lock where you want it, as per your question.