SQL server Functions, stored procedures, cursors, nested use of while and if statements, while nesting

SQL server Functions, stored procedures, cursors, nested use of while and if statements, while nesting

The main reason for writing this stored procedure is that you are too lazy to write data into the table every day. Not only learning, but also training.

USE [oa erp]
GO
/***** Object: StoredProcedure [dbo]. [s_Get_AutoYh] Script Date: 02/11/2015 17:17:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ===================================================== ======
-- Author: <Author, ljq>
-- Create date: <Create Date, 2015-2-1>
-- Description: <Description, which automatically generates maintenance records for all maintenance items within the specified time range of a store.>

-- ===================================================== ======
Alter procedure [dbo]. [s_Get_AutoYh]

@ Sdtm datetime = null, -- Start Time
@ Edtm datetime = null, -- End Time
@ DepoID char (10) = null,
@ UserID char (20) = null,
@ Type varchar (30) = null,
-- Number of affected rows
@ Num int = null output
AS
-- General drug maintenance records (ybyh)
If (@ type = 'ybyh ')
BEGIN
 
-- Create a temporary table
Select a. * into # tmpyh from dbo. GetGoodsBatchListUnReal (@ DepoID, '') a inner join ggoods B on a. goodsid = B. goodsid
Left join gGoodsExt c on B. goodsid = c. goodsid
Where a. amount> 0
And B. catid like '20140901'
And c. storeid! = '07'
Order by a. effdate


Declare @ days int -- calculates the number of days
Declare @ count int -- count the data to be maintained
Declare @ Idx int
Declare @ n int -- record the data to be inserted every day
Declare @ m int -- record the number of execution days
Declare @ Doccode varchar (30)
Declare @ GoodsID char (10)
Declare @ Amount decimal (18, 6)
Declare @ BatchNo varchar (40)
Declare @ EffDate datetime
Declare @ StockArea char (2)
Declare @ Station char (2)
Declare @ CheckResult char (2)
Declare @ DoMethod char (2)
Declare @ MakID char (10)
Declare @ MakDtm datetime
 
-- The difference between the start time and the end time is the number of days for the maintenance record to be added this time.
Set @ days = datediff (day, @ sdtm, @ edtm)
If @ days = 0
Begin
Set @ days = 1
End
-- Get the total number of records, that is, all types of current store
Select @ count = count (*) from # tmpyh
-- Set the number of records per day and rounded down.
Set @ n = floor (@ count/30)

Set @ m = 1

-- Start transaction
-- Begin tran
Begin try



Set @ makdtm = @ sdtm
While (@ days> 0)
Begin


Declare ybyh cursor global -- defines a cursor
Read_only
For select goodsid, batchno, effdate, amount from # tmpyh
Open ybyh

---- If the number of days of execution is within the input time range
-- While (@ m <= @ days)
-- Begin
-- The loop ends every 30 days.
-- If (@ m % 30! = 0)
-- BEGIN
-- Open the cursor
-- Assign a value to the parameter @ goodsid, @ batchno, @ effdate, @ amount
Fetch next from ybyh into @ goodsid, @ batchno, @ effdate, @ amount
-- Execute the first record of the cursor
-- = Fetch next from ybyh

Exec [s_Get_DocCodeSerial_ByTradeID] @ TradeID = '60', @ depoID = @ DepoID, @ DocCode = @ DocCode output
While (@ fetch_status = 0)
BEGIN
-- Execute the data to be inserted every day and jump out of the loop when @ n is less than zero
-- While (@ n> 0)
-- Begin

-- If not exists (select * from zGspyhRecord where goodsid = @ goodsid and isnull (batchno, '') = isnull (@ batchno,'') and isnull (effdate ,'') = isnull (@ effdate ,''))
-- Begin

Select @ stockarea = case a. otcid
When '01 'then' 00'
When '02 'then' 01'
When '03 'then' 02'
End
From ggoods a where goodsid = @ goodsid
Select @ Idx = isnull (max (Idx), 0) + 1 from zGspyhRecord where DocCode = @ DocCode

-- Set the time and nurse Information
Set @ MakID = @ UserID
-- Select DATEADD (dd, 5550, '2017-02-12 11:11:13 ')
Insert into zGspyhRecord (DocCode, DepoID, Idx, GoodsID, Amount, BatchNo, EffDate, StockArea, Station, CheckResult, DoMethod, MakID, MakDtm, CheID, CheDtm)
Values (@ DocCode, @ DepoID, @ Idx, @ GoodsID, @ Amount, @ BatchNo, @ EffDate, @ stockarea, '00', '00', '03 ', @ MakID, @ MakDtm, null, null)

Set @ n = @ n-1
-- Add the idx number on the second as the second of the New Time
Set @ Idx = @ Idx + 1
Set @ MakDtm = DATEADD (SS, @ Idx, @ MakDtm)

Print ('today had' + str (@ n ))
If @ n <= 0
Begin
Exec [s_Get_DocCodeSerial_ByTradeID] @ TradeID = '60', @ depoID = @ DepoID, @ DocCode = @ DocCode output
Set @ MakDtm = DATEADD (DD, 1, @ MakDtm)
Set @ days = @ days-1
Set @ n = floor (@ count/30)

If (@ days % 30 = 0)
Begin
Print ('days: '+ str (@ days ))
Break
End
End
-- End
-- Execute the cursor to obtain data until all data sets are read.
Fetch next from ybyh into @ goodsid, @ batchno, @ effdate, @ amount

-- End
-- END

-- Set @ m = @ m + 1 -- set the number of days to increase by 1 day.
-- Set @ MakDtm = DATEADD (DD, 1, @ MakDtm)
END

Close ybyh -- close the cursor
Deallocate ybyh -- delete a cursor
 
End

-- Returns the execution record. If it is greater than 0, the execution is successful.
Set @ num = @ rowcount
Select @ num
-- Commit tran
-- End the transaction
End try

Begin catch
Print error_message ()
-- Rollback tran
End catch
END