Sharing multi-level database classification code (MSSQL Stored Procedure Edition)

Description

I believe that multi-level classification will be used in any information system, and many versions can be found online. The following is based on the MSSQL Stored Procedure edition,

There are still versions of VB and C # in hand, but over the past few years, the database has been using MSSQL, but the programming language is from VBScript to C # and Pb, now, this classification code is implemented using VB, C #, and Pb, which wastes a lot of time. nnd Shenma databases !!! Brother was fooled.

The classification adopts the prefix encoding method, and the encoding uses the string type. Of course, the binary implementation is also used.

Table Structure
Description (Table category, classid, classname, code is a classification-related field, datanum, info, etc. are added or decreased according to the specific situation)

Stored Procedure

 

-- ***************************** -- Multi-level classified storage process -- wdfrog -- http://wdfrog.cnblogs.com --******************************--******* * ******************** -- data table definition --************* * **************** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category] ') and objectproperty (ID, N 'isusertable') = 1) Drop table [DBO]. [category] gocreate table [DBO]. [category] ([classid] [int] Not nu Ll, [classname] [nvarchar] (50) Collate chinese_prc_ci_as not null, [Code] [nvarchar] (200) Collate chinese_prc_ci_as not null, [datanum] [int] Null, [info] [nvarchar] (1000) Collate chinese_prc_ci_as null) on [primary] goalter table [DBO]. [category] add constraint [df_category_datanum] default (0) for [datanum], constraint [pk_category] primary key clustered ([classid]) on [primary] Go --********** * ************* -- Add a stored procedure for classification --******************* * ******* if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_add] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_add] gocreate proc category_add @ classname nvarchar (50), @ datanum int, @ info nvarchar (1000 ), @ parentid int -- 0 indicates the root category asdeclare @ editcode intdeclare @ steplen intdeclare @ matchstr nvarchar (50) Declare @ typecode nvarchar (50) Declare @ code nvarchar (200) Declare @ mycode nvarchar (200) Declare @ parentcode nvarchar (200) declare @ selfcode intset @ editcode = 1 Set @ steplen = 4 set @ matchstr = replicate ('_', @ steplen) -- 4 _ set @ typecode = ''set @ code ='' set @ mycode = ''set @ selfcode = 0 set @ parentcode ='' select @ parentcode = code from [CATEGORY] Where classid = @ parentidif (@ editcode = 1) begin -- Obtain the code, column. par In entcode + matchstr, select top 1 @ mycode = code from [category] Where code like @ parentcode + @ matchstr order by code descif @ rowcount> 0 beginset @ selfcode = cast (right (@ mycode, @ steplen) as INT) + 1 Set @ typecode = replicate ('0', @ StepLen-1) + Cast (@ selfcode as nvarchar) set @ typecode = right (@ typecode, @ steplen) set @ typecode = @ parentcode + @ typecode endelse beginset @ typecode = @ parentcode + replicate ('0', @ s TepLen-1) + '1' end enddeclare @ classid intset @ classid = 0 -- get the maximum classid select @ classid = max (classid) from [category] if not @ classid is null begin set @ classid = @ classid + 1 end else begin set @ classid = 1 end insert into [category] (classid, classname, code, datanum, Info) values (@ classid, @ classname, @ typecode, @ datanum, @ info) select @ classid as classid go -- ********************* -- modify the stored procedure of a category --******* ******** * ***** If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_update] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_update] gocreate proc category_update @ classid int, -- the classid to be modified @ classname nvarchar (50), @ info nvarchar (1000 ), @ parentid int asdeclare @ editcode intdeclare @ steplen intdeclare @ matchstr nvarchar (50) Declare @ typecode nvarchar (50) Decl Are @ code nvarchar (200) Declare @ mycode nvarchar (200) Declare @ parentcode nvarchar (200) declare @ selfcode intset @ editcode = 0 set @ steplen = 4 set @ matchstr = replicate ('_', @ steplen) -- 4 _ set @ typecode = ''set @ code ='' set @ mycode = ''set @ selfcode = 0 set @ parentcode ='' select @ parentcode = code from [CATEGORY] Where classid = @ parentidselect @ code = code from [category] Where classid = @ classid -- modify the original category -- determine whether to modify the code field -- View Yes No changed direct parent category (upper level) If @ parentcode! = Left (@ code, Len (@ Code)-@ steplen) begin -- filter and select as the parent class if (@ parentcode! = @ Code) begin -- filter and select its own subclass as the parent class if Len (@ parentcode)> Len (@ code) begin -- because Len (@ parentcode)> Len (@ code) so you can left (@ parentcode, Len (@ Code) If left (@ parentcode, Len (@ Code ))! = @ Code -- if they are equal, select your own subclass as the parent class begin set @ editcode = 1 endend elsebegin set @ editcode = 1end endendif (@ editcode = 1) begin -- Obtain the code, column. select top 1 @ mycode = code from [category] Where code like @ parentcode + @ matchstr order by code DESC in parentcode + matchstr -- whether there is a subclass if @ rowcount> 0 beginset @ selfcode = cast (right (@ mycode, @ steplen) as INT) + 1 Set @ typecode = replicate ('0', @ StepLen-1) + Cast (@ selfcode Nvarchar) set @ typecode = right (@ typecode, @ steplen) set @ typecode = @ parentcode + @ typecode endelse -- no subclass so the number starts from 1 beginset @ typecode = @ parentcode + replicate ('0', @ StepLen-1) + '1' end endif (@ editcode = 1) Begin update [category] Set classname = @ classname, code = @ typecode, info = @ info where classid = @ classid endelse begin update [category] Set classname = @ classname, info = @ info where classid = @ classid end --- modify the subclass ID (Code) if (@ editcode = 1) Begin update [category] Set code = @ typecode + right (Code, Len (CODE)-len (@ Code )) where code like @ code + '%' endgo --***************************** * ****** -- delete a category, you can only delete a category without subclass --******************************** * *** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_del] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_del] gocres Ate proc category_del @ classid int Asif (select count (classid) from [category] Where code like (select code from [category] Where classid = @ classid) + '%' and classid <> @ classid)> 0 begin raiserror ('class with subclasses cannot be deleted ', 200) return enddeclare @ code nvarchar) declare @ value intset @ value = 0 select @ code = [Code], @ value = [datanum] from [category] Where [classid] = @ classidupdate [category] Set [datanum] = [datanum] -@ Value where [classid] In (select classid from [category] where Len (CODE) <= Len (@ code) and code = left (@ code, len (CODE ))) delete from category where classid = @ classid go -- ************************** -- obtain by number A category record -- ************************* if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_select] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [Category _ Select] gocreate procedure category_select @ classid intasselect [classid], [classname], [Code], [datanum], [info] from [category] Where [classid] = @ classidgo --************************* * -- Sort a mobile category -- ***************************** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_move] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_move] gocreate pro C category_move @ classid int, @ ISUP bit = 1 asdeclare @ maskstr nvarchar (200) Declare @ tempstr nvarchar (200) Declare @ code nvarchar (200) set @ code = ''set @ tempstr ='' select @ code = code from [category] Where classid = @ classidset @ maskstr = replicate (N '-', len (@ Code) If @ code! = ''And (LEN (@ code) % 4) = 0) begin if (@ ISUP = 1) begin if (LEN (@ code)> 4) begin select top 1 @ tempstr = code from [category] where Len (CODE) = Len (@ code) and Code <@ code and left (Code, Len (CODE)-4) = left (@ code, Len (@ Code)-4) Order by code desc end else begin select top 1 @ tempstr = code from [category] where Len (CODE) = Len (@ code) and Code <@ code order by code desc end else begin if (LEN (@ code)> 4) begin select top 1 @ tempstr = codefrom [category] where Len (CODE) = Len (@ code) and code> @ code and left (Code, Len (CODE)-4) = left (@ code, Len (@ Code)-4) Order by code ASC end else begin select top 1 @ tempstr = code from [category] where Len (CODE) = Len (@ code) and code> @ code order bycode ASC end -- // It is already the beginning (last) if @ tempstr is null or rtrim (ltrim (@ tempstr) = ''begin return enddeclare @ codelen intdeclare @ maxlen intset @ codelen = Len (@ code) set @ maxlen = 200 -- // set the target class, And the subclass of the target class is ---- 0001 (target class) or ---- 00010002 (subclass) update [category] Set code = @ maskstr + substring (Code, @ codelen + 1, @ maxlen) Where left (Code, @ codelen) = @ tempstr -- // update the current switching class (including subclasses). The Code is the target class codeupdate [category] Set code = @ tempstr + substring (Code, @ codelen + 1, @ maxlen) where left (Code, @ codelen) = @ code -- // update the code of the target class (including subclasses) to the current switch class codeupdate [category] Set code = @ code + substring (code, @ codelen + 1, @ maxlen) Where left (Code, @ codelen) = @ maskstrgo -- **************************** -- Obtain the parent category information of a specified category -- * *************************** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_queryparent] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_queryparent] gocreate proc category_queryparent @ classid intasdeclare @ classcode nvarchar (200) Select @ classcode = code from [category] Where classid = @ classidselect classid, classname, code, datanum from [category] where Len (CODE) <= Len (@ classcode) and code = left (@ classcode, Len (CODE )) order by code go -- ****************************** -- get the entire category directory -- ***************************** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_query] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_query] gocreate proc category_queryasselect [classid], [classname], [Code], [datanum] from [category] Order by [Code] Go --************************** * ** -- reset all categories to root categories -- *************************** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_reset] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_reset] gocreate proc category_resetasdeclare @ code nvarchar (200) declare @ I intset @ code = ''set @ I = 1 declare category_cursor cursor forselect code from [category] Open category_cursorfetch next from category_cursorwhile @ fetch_status = 0 begin set @ code = replicate (N '0 ', 4) + Cast (@ I as nvarchar) set @ code = right (@ code, 4) update [category] Set code = @ code where current of category_cursor set @ I = @ I + 1 fetch next from category_cursorendclose category_cursordeallocate category_cursorgo --************ * ******** -- get the category name of the specified category --*********************** * If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_selectclassname] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_selectclassname] gocreate proc category_selectclassname @ classid intasselect [classname] from [category] Where [classid] = @ classidgo --**************** * *** -- obtains the subclass of a specified class, it also includes its own -- ******************** if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_querychildren] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_querychildren] gocreate proc category_querychildren @ classid intasdeclare @ code nvarchar (200) Select @ code = [Code] from [category] Where [classid] = @ classidselect [classid], [classname], [Code], [datanum] from [category] Where code like @ code + '%' order by code go --******************* * ** -- Obtain the list of first-level classes -- ********************* if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [category_queryroot] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [category_queryroot] gocreate proc category_queryrootasselect [classid], [classname], [Code], [datanum] from [category] where Len (CODE) = 4 order by code go