Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ALTER PROCEDURE spCreateDefective
- (
- @alloc_ID nvarchar(50),
- @cur_date datetime,
- @problem_desc nvarchar(MAX),
- @got_defect_date datetime,
- @trans_status tinyint OUTPUT --Used to check transaction status
- )
- AS
- /* Transaction Maintainer Statements */
- BEGIN TRAN transac1
- SET XACT_ABORT ON
- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
- /* Declaration Area */
- DECLARE @temp nvarchar(10)
- DECLARE @def_ID nvarchar(20)
- /* Updating Inventory Status to 'Defective' and Updating Release Date to current System date */
- UPDATE INV_Allocation_DB
- SET INV_STATUS = 'Defective' , RELEASE_DATE=@cur_date
- WHERE INV_ID=@alloc_ID
- /* Calculates New Primary Key for the DEFECT_LOG Table */
- -- Returns the largest number or 1 if no records are present in the table
- SELECT @temp=COALESCE(CONVERT(int,SUBSTRING(MAX(DEFECT_ID),5,5))+1,1) FROM DEFECT_LOG
- SET @def_ID = 'DEF_'+ RIGHT(replicate('0',5)+ convert(varchar(5),@temp),5)
- /* Insert statement for inserting data into DEFECT_LOG */
- INSERT INTO DEFECT_LOG (DEFECT_ID,INV_ID,PROB_DESC,GOT_DEFECT_DATE)
- VALUES(@def_ID,@alloc_ID,@problem_desc,@got_defect_date)
- SET @trans_status = 1
- COMMIT TRAN transac1
- /* Returns 1 if transaction successful */
- RETURN @trans_status
Add Comment
Please, Sign In to add comment