Insert with cursor in the Stored Procedure (product category)

Example: insert multiple categories into the commodity classification table.

Create procedure tproduct_add
@ Proid bigint output, -- automatic number
@ ISBN nvarchar (50 ),
@ Pcode nvarchar (100 ),
@ Pname nvarchar (200 ),
@ Unit nvarchar (50 ),
@ Price decimal (18,2 ),
@ Purchaseprice decimal (18, 2 ),
@ Discount decimal (4, 2 ),
@ Membeprice decimal (18,2 ),
@ Membeprice2 decimal (18,2 ),
@ Press nvarchar (100 ),
@ Presstime nvarchar (50 ),
@ Editor nvarchar (200 ),
@ Teditor nvarchar (200 ),
@ Stocknumber int,
@ Prosmallimage nvarchar (200 ),
@ Probiglimage nvarchar (200 ),
@ Brief fintro nvarchar (300 ),
@ Remark nvarchar (max ),
@ Ishot char (1 ),
@ Isnew char (1 ),
@ Istop char (1 ),
@ Isbargain char (1 ),
@ Createtime datetime,
@ Modifytime datetime,
@ Addedby nvarchar (200 ),
@ Isbuy nchar (1 ),
@ Returnprice decimal (18,2 ),
@ Ischeck char (1 ),
@ Storehouseid nvarchar (50 ),
@ Delflag char (1 ),
@ Editionnum nvarchar (50 ),
@ Pcategriy nvarchar (50) -- 2, 8, 4

As
Insert into [tproduct] (
[ISBN], [pcode], [pname], [unit], [price], [purchaseprice], [discount], [membeprice], [membeprice2], [press], [presstime], [Editor], [teditor], [stocknumber], [prosmallimage], [probiglimage], [briefintro], [Remark], [ishot], [isnew], [Istop], [isbargain], [createtime], [modifytime], [addedby], [isbuy], [returnprice], [ischeck], [storehouseid], [delflag], [editionnum]
) Values (
@ ISBN, @ pcode, @ pname, @ unit, @ price, @ purchaseprice, @ discount, @ membeprice, @ membeprice2, @ press, @ presstime, @ Editor, @ teditor, @ stocknumber, @ prosmallimage, @ probiglimage, @ briefintro, @ remark, @ ishot, @ isnew, @ Istop, @ isbargain, @ createtime, @ modifytime, @ addedby, @ isbuy, @ returnprice, @ ischeck, @ storehouseid, @ delflag, @ editionnum
)
Set @ proid = @ identity

-- Then add records to the product category table

-- We need to separate 2, 8, and 4 items.
Declare @ _ classid bigint
Declare rebate cursor -- defines a kickback cursor
For select * From DBO. f_split (@ pcategriy, ',') // call the function to split the string (Multiple ID numbers) using ','

Open rebate

Fetch next from rebate into @ _ classid
Declare @ I int;
Set @ I = 1;
While @ fetch_status = 0
Begin
Insert into [tproduct_category] ([productid], [pcategory]) values (@ proid, @ _ classid)
Fetch next from rebate into @ _ classid
End
-- 4. Close the cursor
Close rebate
-- 5. delete a cursor
Deallocate rebate

// Create the f_split Function

Create Function [DBO]. [f_split] (@ C varchar (2000), @ split varchar (2 ))
Returns @ t table (COL varchar (20 ))
As
Begin

While (charindex (@ split, @ C) <> 0)
Begin
Insert @ T (COL) values (substring (@ C, 1, charindex (@ split, @ C)-1 ))
Set @ C = stuff (@ C, 1, charindex (@ split, @ C ),'')
End
Insert @ T (COL) values (@ C)
Return
End