Comprehensive application of the cursors of MSSQL

Use [CHIEFWMS]
GO
/****** object:storedprocedure [dbo]. [Wms_check] Script date:04/05/2016 09:51:13 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Author:cj
date:2016-03-09
Original stored procedure name: dbo. Wms_check
Anti-audit function of product assembly list
*/
ALTER procedure [dbo]. [Wms_check]
(
[email protected] nvarchar = ' zd1233-350,zd2993b-314 ',
[Email protected] nvarchar (200) = ' 10,10 ',
----Plus Warehouse
[Email protected] nvarchar (200) = ' 172 warehouse, 172 warehouse '

@ItemNo nvarchar (200) = ', ',
@OutQty nvarchar (200) = ', ',
--Add Warehouse
@StockNo nvarchar (200) = ', '
)
As
Begin

--declare @ItemNo nvarchar (+) = ' zd1233-350,zd2993b-314 '
--declare @OutQty nvarchar (200) = ' 200,200 '

DECLARE @Temp_Table Table
(
ID int identity (+),
ItemNo nvarchar (20),
Outqty int,
Stockno nvarchar (20)
)

if (@ItemNo <> ', ' and @OutQty <> ', ' and @StockNo <> ', ')
Begin
--select * into #Temp_ItemNo from dbo. Fn_splitstr (' 810647486,810647485,810647484,810647483 ', ', ')
--select * into #Temp_OutQty from dbo. Fn_splitstr (' 123,423,562,147 ', ', ')

SELECT * Into #ItemNo_Table from dbo. Fn_splitstr (@ItemNo, ', ')
SELECT * Into #OutQty_Table from dbo. Fn_splitstr (@OutQty, ', ')
SELECT * Into #StockNo_Table from dbo. Fn_splitstr (@StockNo, ', ')

Insert @Temp_Table
Select A.col,b.col,c.col from #ItemNo_Table A
Join #OutQty_Table B on A.id=b.id
Join #StockNo_Table C on A.id=c.id

--select * from @Temp_Table
End

Else
Begin
Select ' Product data not complete! ‘
Return
End

----determine that a certain number of out-of-stock is greater than the total number of inventory to return to the front end to inform an item cannot out of library out of library number insufficient
DECLARE @SumStockQty int

declare @Check_ItemNo nvarchar (20)
DECLARE @Check_OutQty int
declare @Check_StockNo nvarchar (20)

DECLARE check_cur cursor Local
For
Select Itemno,outqty,stockno from @Temp_Table
Open Check_cur
Fetch next from Check_cur to @Check_ItemNo, @Check_OutQty, @Check_StockNo
while (@ @FETCH_STATUS =0)
Begin

If not EXISTS (SELECT * FROM dbo. Wms_stockdetail where [email protected]_itemno and [email Protected]_stockno]
Begin
Select ' Part number: ' [email protected]_itemno+ ' In the number: ' [email protected]_stockno+ ' Warehouse No inventory please change the other warehouse! '
Return
End

Select @SumStockQty =sum (stockqty) from dbo. Wms_stockdetail where [email protected]_itemno and [email protected]_stockno
if (@Check_OutQty > @SumStockQty)-The number of out-of-stock is greater than the total
Begin
Select ' Part number: ' [email protected]_itemno+ ' Out of the Library: ' +cast (@Check_OutQty as nvarchar (20) + ' greater than warehouse ' [email protected]_stockno+ ') Stock Count ' +cast (@SumStockQty as nvarchar ()) as Result
Return
End
--select ' Dsfds '
Fetch next from Check_cur to @Check_ItemNo, @Check_OutQty, @Check_StockNo
End
Close Check_cur
Deallocate check_cur
-----------------------------------------------------------------Judgment End

declare @Temp_ItemNo nvarchar (20)
DECLARE @Temp_OutQty int
declare @Temp_StockNo nvarchar (20)

DECLARE @Result int =0
DECLARE first_cur cursor Local
For
Select Itemno,outqty,stockno from @Temp_Table

Open First_cur
Fetch next from First_cur to @Temp_ItemNo, @Temp_OutQty, @Temp_StockNo
while (@ @FETCH_STATUS =0)
Begin

SELECT * Into #TempWMSCheck from dbo. Wms_stockdetail where [email protected]_itemno and [email protected]_stockno ORDER BY createdate ASC
--select * from #TempWMSCheck
DECLARE @ID int
DECLARE @StockQty int

DECLARE mycursor cursor Local
for
Select Id,stockqty from #TempWMSCheck

Open MyCursor
Fetch next from MyCursor to @ID, @StockQty
while (@ @Fetch_Status = 0)
Begin
--9000-8590=410

if (@StockQty >0)
Begin

if (@[email protected]>0)

Begin
UPDATE dbo. Wms_stockdetail
Set stockqty=0 where [email protected]

Set @[email Protected][email protected]

End

Else

Begin
UPDATE dbo. Wms_stockdetail
Set [email protected] @Temp_OutQty
where [email protected]
--select @Result as Result
--return (jump directly out of the loop so you can't use return here)
break--interrupt this cycle for the next cycle outside when the condition is met
End
End



Else
Begin
Set @Result =1
End

Fetch next from MyCursor to @ID, @StockQty
End
Close MyCursor
Deallocate mycursor

drop table #TempWMSCheck

Fetch next from First_cur to @Temp_ItemNo, @Temp_OutQty, @Temp_StockNo
End
Close First_cur
Deallocate first_cur

Delete from @Temp_Table
drop table #ItemNo_Table
drop table #OutQty_Table
drop table #StockNo_Table
Select @Result as Result

End

Comprehensive application of the cursors of MSSQL