code_junkie

Stored Procedure Serialization Problem

Nov 13th, 2011
125
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.27 KB | None | 0 0
  1. ALTER PROCEDURE spCreateDefective
  2.  
  3. (
  4.  
  5. @alloc_ID nvarchar(50),
  6. @cur_date datetime,
  7. @problem_desc nvarchar(MAX),
  8. @got_defect_date datetime,
  9. @trans_status tinyint OUTPUT --Used to check transaction status
  10. )
  11.  
  12. AS
  13.  
  14. /* Transaction Maintainer Statements */
  15.  
  16. BEGIN TRAN transac1
  17.  
  18. SET XACT_ABORT ON
  19.  
  20. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  21.  
  22. /* Declaration Area */
  23.  
  24. DECLARE @temp nvarchar(10)
  25.  
  26. DECLARE @def_ID nvarchar(20)
  27.  
  28. /* Updating Inventory Status to 'Defective' and Updating Release Date to current System date */
  29.  
  30. UPDATE INV_Allocation_DB
  31.  
  32. SET INV_STATUS = 'Defective' , RELEASE_DATE=@cur_date
  33. WHERE INV_ID=@alloc_ID
  34.  
  35.  
  36. /* Calculates New Primary Key for the DEFECT_LOG Table */
  37.  
  38. -- Returns the largest number or 1 if no records are present in the table
  39.  
  40. SELECT @temp=COALESCE(CONVERT(int,SUBSTRING(MAX(DEFECT_ID),5,5))+1,1) FROM DEFECT_LOG
  41.  
  42.  
  43. SET @def_ID = 'DEF_'+ RIGHT(replicate('0',5)+ convert(varchar(5),@temp),5)
  44.  
  45. /* Insert statement for inserting data into DEFECT_LOG */
  46.  
  47. INSERT INTO DEFECT_LOG (DEFECT_ID,INV_ID,PROB_DESC,GOT_DEFECT_DATE)
  48. VALUES(@def_ID,@alloc_ID,@problem_desc,@got_defect_date)
  49.  
  50.  
  51. SET @trans_status = 1
  52.  
  53. COMMIT TRAN transac1
  54.  
  55. /* Returns 1 if transaction successful */
  56.  
  57. RETURN @trans_status
Add Comment
Please, Sign In to add comment