Automatic Coding of multi-level classified archive ID numbers in the basic archive management module of ERP (V1.0)

Automatic Coding of multi-level classified archive ID numbers in the basic archive management module of ERP (V1.0)

 

This Stored Procedure implements the automatic encoding technology for multi-level classified file ID numbers. This version (V1.0) now only implements three-bit encoding at each level,

This version has the following features:

Different codes can be generated based on different database tables to achieve generalization.

During the call, you can specify whether the node encoding to be generated by iissubnode is a subnode or a sibling node to generate the corresponding encoding.

When calling this stored procedure, you must note that you need to pass the node hierarchy (or node depth)

In addition, the next version (V2.0) will implement more flexible automatic encoding technology based on the custom length of each level.

 

Create procedure prcidautogen

@ Vsourceid varchar (30 ),

@ Idepth int,

@ Iissubnode int,

@ Table varchar (20 ),

@ Vincrement varchar (30) Output

As

Begin

Declare @ ilen int

Declare @ vtempid varchar (30)

Declare @ sqlstring nvarchar (500)

If @ iissubnode = 1

Begin

Set @ idepth = @ idepth + 1

Set @ ilen = @ idepth * 3

Set @ sqlstring = N "select vid from" + @ table + "where vid =" + ltrim (rtrim (@ vsourceid) + """"

Exec (@ sqlstring)

If @ rowcount> 0

Begin

Select @ vsourceid as vid into # T

Set @ sqlstring = N "insert # T select vid from" + @ table + "where vparentid in (select vid from # T) and vid not in (select vid from # T) and idepth = @ idepth"

Exec sp_executesql @ sqlstring, N "@ idepth int", @ idepth

If @ rowcount> 0

Begin

Set @ sqlstring = N "select @ vtempid = isnull (max (VID)," 0 "") from # t"

Exec sp_executesql @ sqlstring, N "@ vtempid varchar (30) Output", @ vtempid output

Set @ sqlstring = "select @ vincrement = right (" "000" "+ Cast (cast (substring (@ vtempid, 1, @ ilen)
Decimal (30,0) + 1) as varchar), @ ilen )"

Exec sp_executesql @ sqlstring, N "@ vincrement varchar (30) output, @ vtempid varchar (30), @ ilen int", @ vincrement out, @ vtempid, @ ilen

End

Else

Begin

Select @ vincrement = ltrim (rtrim (@ vsourceid) + "001"

End

End

Else

Begin

Select @ vincrement = "001"

End

End

Else

Begin

Set @ ilen = Len (ltrim (rtrim (@ vsourceid )))

Set @ sqlstring = N "select vid from" + @ table + "where vid =" + ltrim (rtrim (@ vsourceid) + """"

Exec (@ sqlstring)

If @ rowcount> 0

Begin

Set @ sqlstring = N "select @ vtempid = isnull (max (VID)," 0 "") from "+ @ table +" where VID in (select vid from "+ @ table +" where idepth = @ idepth )"

Exec sp_executesql @ sqlstring, N "@ vtempid varchar (30) output, @ idepth int", @ vtempid output, @ idepth

Set @ sqlstring = "select @ vincrement = right (" "000" "+ Cast (cast (substring (@ vtempid, 1, @ ilen) as decimal (30,0 )) + 1) as varchar), @ ilen )"

Exec sp_executesql @ sqlstring, N "@ vincrement varchar (30) output, @ vtempid varchar (30), @ ilen int", @ vincrement out, @ vtempid, @ ilen

End

Else

Begin

Select @ vincrement = "001"

End

End

End

 

You can create a basic file in a table like the following:

Create Table customerclass (

Vid varchar (30) Constraint pkcustomerclass primary key,

Vcustomerclassname varchar (40) not null,

Vremarks varchar (80) null,

Vparentid varchar (30) null,

Idepth int not null

)

 

In addition, to test the SQL query analyzer, use the following method:

Declare @ value varchar (30)

Exec prcidautogen "", 0, 1, "customerclass", @ vincrement = @ value output

Select @ Value

 

Insert customerclass values ("001", "A", "A", null, 1)

 

Declare @ value varchar (30)

Exec prcidautogen "001", 1,1, "customerclass", @ vincrement = @ value output

Select @ Value

 

Insert customerclass values ("001001", "B", "B", "001", 2)

 

Declare @ value varchar (30)

Exec prcidautogen "001", 1,1, "customerclass", @ vincrement = @ value output

Select @ Value

 

Declare @ value varchar (30)

Exec prcidautogen "001001", 2, 0, "customerclass", @ vincrement = @ value output

Select @ Value

 

And so on. Note that the three statements are executed together)