Stored Procedure usage tips

==================== The following are stored procedures ========================
/*
Add a basic message
Inventory items

-1 inttostr failed
-2 related data exists
-3 parid does not exist
-4 The record already exists.
*/

Create procedure f_ B _insertp
(@ Parid varchar (25 ),
@ Dbname varchar (30 ),
@ Fullname varchar (66 ),
@ Namevarchar (30 ),
@ Usercode varchar (26 ),
@ Standard varchar (120 ),
@ Type varchar (40 ),
@ Area varchar (30 ),
@ Unit1 varchar (8 ),
@ Unit2 varchar (8 ),
@ Unitrate1 numeric (18, 4 ),
@ Unitrate2 numeric (18, 4 ),
@ Preprice1 numeric (18, 4 ),
@ Preprice2 numeric (18, 4 ),
@ Lifemonth int,
@ Lifeday int,
@ Comment varchar (256 ),
@ Namepyvarchar (60 ),
@ Barcodevarchar (30)
)
As
Declare @ nreturntype int
Declare @ etypeid_1 varchar (25)
Declare @ nsoncount int
Declare @ nsonnum int
Declare @ leveal smallint
Declare @ parrec int
Declare @ checkvalue int

-- Get the ID number

Exec @ nreturntype = f_ B _createid @ parid, @ dbname, @ etypeid_1 out, @ nsonnum out, @ nsoncount out,

@ Parrec out

If @ nreturntype =-101 goto error111
If @ nreturntype =-102 goto error112
If @ nreturntype =-103 goto error113

exec @ checkvalue = mzw_checkbasicset
If @ checkvalue = 1 -- the search ID and full name cannot be exactly the same
begin
If exists (select [typeid] From ptype where [typeid] = @ etypeid_1 or ([fullname] = @ fullname
and [usercode] = @ usercode) and [deleted] <> 1) goto error114
end
If @ checkvalue = 2 -- the full name cannot be the same
begin
If exists (select [typeid] From ptype where [typeid] = @ etypeid_1 or ([fullname] = @ fullname
) and [deleted] <> 1) goto error114
end
If @ checkvalue = 3 -- the numbers cannot be exactly the same
begin
If exists (select [typeid] From ptype where [typeid] = @ etypeid_1 or (
[usercode] = @ usercode) and [deleted] <> 1) goto error114
end
If @ checkvalue = 4 -- the search ID and full name cannot be exactly the same
begin
If exists (select [typeid] From ptype where [typeid] = @ etypeid_1 or ([fullname] = @ fullname
or [usercode] = @ usercode) and [deleted] <> 1) goto error114
end
If (LEN (@ barcode)> 0)
begin
If exists (select * From ptype
where ([barcode] = @ barcode) and [deleted] <> 1) return-5
end

Begin Tran insertp

Select @ leveal = [leveal] From ptype where [typeid] = @ parid
Select @ leveal = @ leveal + 1

Insert into [ptype]
([Typeid], [parid], [leveal], [soncount], [sonnum],
[Fullname], [name], [usercode], [standard], [type],
[Area], [unit1], [unit2], [unitrate1], [unitrate2],
[Preprice1], [preprice2], [usefullifemonth],
[Usefullifeday], [comment], [namepy], [parrec],
[Barcode])

Values (@ etypeid_1, @ parid, @ leveal, 0, 0,
@ Fullname, @ name, @ usercode, @ standard, @ type,
@ Area, @ unit1, @ unit2, @ unitrate1, @ unitrate2,
@ Preprice1, @ preprice2, @ lifemonth,
@ Lifeday, @ comment, @ namepy, @ parrec,
@ Barcode)

If @ rowcount = 0
Begin
Rollback Tran insertp
Return-1
End
Else
Begin
Update [ptype]
Set [sonnum] = @ nsonnum + 1, [soncount] = @ nsoncount + 1
Where [typeid] = @ parid
End

Commit Tran insertp

Goto succee

Succee:
Return 0

Error111:
Return-1

Error112:
Return-2

Error113:
Return-3

Error114:
Return-4
Go

 

//// // Use the cursor

If Len (@ parid) = 25 return-1
Declare @ execsql [varchar] (500)
Declare @ sztypeid varchar (25), @ sonnum int, @ par varchar (25), @ soncount int
Select @ execsql = 'select typeid, sonnum, parid, soncount from'
+ @ Dbname + 'where typeid = '+ ''' + @ parid + ''''

declare checkid_cursor cursor for
exec (@ execsql)
open checkid_cursor
fetch next from checkid_cursor into @ sztypeid, @ sonnum, @ par, @ soncount
while (@ fetch_status = 0)
begin
set @ nson = @ sonnum
set @ ncount = @ soncount
declare @ tempid varchar (5 ), @ nreturn int
set @ soncount = @ soncount + 1
exec @ nreturn = inttostr @ soncount, @ tempid out
If @ nreturn =-1
begin
close checkid_cursor
deallocate checkid_cursor
return-2
end
else
begin
If @ sztypeid = '000000'
set @ createdid = @ tempid
else
set @ createdid = rtrim (@ sztypeid) + @ tempid
end
fetch next from checkid_cursor into @ sztypeid, @ sonnum, @ par, @ soncount
end
close checkid_cursor
deallocate checkid_cursor
return 1