Nested use of SQL Server functions, stored procedures, cursors, and while and if statements

The main reason for writing this stored procedure is that I am too lazy to do the data on the table every day, so I wrote him. Not only study, but also their own exercise.

Use [Oaerp]
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, automatic generation of all maintenance records for a specified time range of a store >

-- =============================================
ALTER PROCEDURE [dbo]. [S_GET_AUTOYH]

@sdtm datetime=null,--Start time
@edtm datetime=null,--End of Time
@DepoID char (=null),
@UserID char (=null),
@type varchar (=null),
--Number of rows affected
@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 ' 1% '
and C.storeid! = ' 07 '
ORDER BY A.effdate


DECLARE @days INT-Calculates the number of days
DECLARE @count INT--Statistics of data to be conserved
DECLARE @Idx int
DECLARE @n INT--records data that needs to be inserted every day
DECLARE @m INT--record number of days to execute
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 and end times is the number of days you need to add maintenance records
Set @days = DateDiff (Day, @sdtm, @edtm)
If @days = 0
Begin
Set @days = 1
End
--Get the total number of records, that is, the current store all the varieties
Select @count = count (*) from #tmpyh
--Set the number of records per day, rounding down.
Set @n=floor (@count/30)

Set @m=1

--Start a transaction
--begin Tran
Begin try



Set @makdtm[email protected]
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 to execute is within the input time range
--while (@m<[email protected])
--begin
----each execution 30 days, the loop is closed.
--if (@m%30!=0)
--BEGIN
--Open cursor
--Assigning values to parameters @goodsid, @batchno, @effdate, @amount
FETCH NEXT from Ybyh to @goodsid, @batchno, @effdate, @amount
--Execute cursor first record
--=fetch Next from Ybyh

exec [S_get_doccodeserial_bytradeid] @TradeID = ' $ ', @[email protected],@[email protected] Output
while (@ @fetch_status = 0)
BEGIN
--Perform the data that needs to be inserted every day and jump out of the loop when @n is less than 0
--while (@n>0)
--begin

--if NOT EXISTS (SELECT * from Zgspyhrecord where goodsid[email protected]and IsNull (Batchno, ') =isnull (@batchno, ') and IsNull (Effdate, ') =isnull (@effdate, '))
--begin

Select @stockarea = Case A.otcid
When ' down ' then ' 00 '
When ' 01 '
When ' the ' Then ' 02 '
End
From Ggoods a where goodsid[email protected]
Select @Idx =isnull (Max (IDX), 0) +1 from Zgspyhrecord where[email protected]

--Set up time and maintenance staff information
Set @[email protected]
--select DATEADD (dd,5550, ' 2015-02-12 11:11:13 ')
Insert into Zgspyhrecord (Doccode, Depoid, IDX, Goodsid, Amount,batchno, Effdate, Stockarea, Station, Checkresult, Dometho D, Makid, Makdtm, Cheid, Chedtm)
VALUES (@DocCode, @DepoID, @Idx, @GoodsID, @Amount, @BatchNo, @EffDate, @stockarea, ' xx ', ' xx ', ' in ', @MakID, @MakDtm, NULL, Null

Set @[email protected]
--Add the IDX ordinal to the second, as the second of the new time
Set @[email protected]+1
Set @MakDtm =dateadd (SS, @Idx, @MakDtm)

Print (' Today had ' +str (@n))
If @n<=0
Begin
exec [S_get_doccodeserial_bytradeid] @TradeID = ' $ ', @[email protected],@[email protected] Output
Set @MakDtm =dateadd (dd,1, @MakDtm)
Set @days[email protected]-1
Set @n=floor (@count/30)

if (@days%30=0)
Begin
Print (' Days: ' +str (@days))
Break
End
End
--end
--Execute cursors to get data until all data sets are read
FETCH NEXT from Ybyh to @goodsid, @batchno, @effdate, @amount

--end
--end

--set @[email protected]+1--set days to increase by 1 days
--set @MakDtm =dateadd (dd,1, @MakDtm)
END

Close Ybyh--closing cursors
Deallocate YBYH--delete cursor

End

--Returns the executed record, if greater than 0, indicating successful execution
Set @num [email protected]@rowcount
Select @num
--commit Tran
--End transaction
End Try

Begin Catch
Print Error_message ()
--rollback Tran
End Catch
END

Nested use of SQL Server functions, stored procedures, cursors, and while and if statements