Use of SQL Server cursors

Use [PatPD1]
GO
/****** object:userdefinedfunction [dbo].    [Fun_getconditioninner] Script DATE:2015/5/9 9:03:38 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo]. [Fun_getconditioninner] (@Product varchar (20))
Returns varchar (8000)
As
Begin
DECLARE @ReturnValue varchar (8000) = "--for stitching the return value
DECLARE @Status varchar (8000)--for storing state values
declare @Type nvarchar ()--eqptype
declare @TempType nvarchar ()--eqptype temporary variables are used to compare
DECLARE @temp table (
Status nvarchar (20),
Type nvarchar (20)
)
--Declare @Product nvarchar (--eqptype)--Test release
--Set @Product = ' 8701BA '
--Set @Product = ' 8725aa-0030a '
Insert INTO @temp select B.status,a.type from [dbo]. [Mtfsetting] A join [dbo]. [mtfdetailsetting] B on a.id=b.id and a.product [email protected]
--Declare that the cursor is Equipmentid,fixqty
Declare mtf_cursor Cursor for select Status,type from @temp
--Open cursor

Open Mtf_cursor
FETCH NEXT from the mtf_cursor into @Status, @Type--moves the cursor down by 1 rows, gets the data into the previously defined variable @status, @JoinCalculate

While @ @FETCH_STATUS = 0--Returns the state of the cursor when FETCH executes the statement (1:fetch gets the data successfully -1:fetch statement failed or this row is not in the result set-2: The fetched row does not exist)
Begin
if (@[email protected])--Dynamic splicing input number
Begin
Set @ReturnValue + = '
SUM (case when eqptype= "+rtrim (@TempType) +" then QUANTITY ELSE 0 END) Qty_total_ ' +replace (@TempType, ', ') + ', '
End

Set @ReturnValue + = '
SUM (case when a.status= "+rtrim (@Status) +" "--stitching conditional header

if (select COUNT (*) from [dbo].[ Mtfsetting]where [email protected]) >1)--a combination of multiple types, multiple splicing of a a.eqptype condition
Begin
Set @ReturnValue + = ' and A.eqptype= ' ' +rtrim (@Type) + ""
End

Set @ReturnValue + = ' then QUANTITY else 0 end) Qty_ ' +rtrim (@Status) + ' _ ' +replace (@Type, ' ', ') + ', '--stitching conditional tail
Set @[email protected] Assign to temporary storage variables
FETCH NEXT from Mtf_cursor into @Status, @Type--Move cursor down 1 rows
End
Close mtf_cursor--Closing Cursors
deallocate mtf_cursor--Release Cursors


if (select COUNT (*) from [dbo].[ Mtfsetting]where [email protected]) >1--a combination of multiple types
Begin
Set @ReturnValue + = '
SUM (case when eqptype= "+rtrim (@TempType) +" then QUANTITY ELSE 0 END) Qty_total_ ' +replace (@TempType, ', ')
End
Else
Begin
Set @ReturnValue + = '
SUM (QUANTITY) Qty_total_ ' +replace (@TempType, ', ')
End
--print @ReturnValue
Return @ReturnValue

End

Use of SQL Server cursors