Exec sp_addtype rowid,'Varchar(6)','Not Null' --自定義數據類型rowid　　標識代碼

Exec sp_addtype orgcode,'Varchar(20)','Not Null'  --自定義數據類型orgcode　　公司代碼
    

      --可根據實際情況設置默認值

CREATE TABLE _sysparameters(

nodetype varchar(10) NOT NULL,	　　　　　　--標識類型

nodeid rowid NOT NULL,			　　--標識的最大值,之后新的ID在這個基礎上遞增

siteid varchar(10),				--默認為NULL,自定義

version char(10)				--版本,自定義

--設置rowid為6位數.創建規則

CREATE RULE rowid6

AS

STR(@rowid)=6

--綁定規則

SP_BINDRULE rowid6,'_sysparameters.nodeid'
    

      /*

	2015.4.26	HZF

*/

ALTER PROC [dbo].[p_sysxxxxrowid]

	@XXXX ROWID OUTPUT

AS

	DECLARE @nodeid rowid

	DECLARE @oldnodeid rowid

	

	BEGIN TRAN

		

		--  ASCII 40 - 96

		select @nodeid = nodeid from _sysparameters where nodetype='rowid'	--獲取歷史最大的id碼

		set @oldnodeid = @nodeid

		

		if RIGHT(@nodeid,1) = '9'

			set @nodeid = left(@nodeid,5) + 'A'

		else if RIGHT(@nodeid,2) = '9Z'

			set @nodeid = left(@nodeid,4) + 'A0'

		else if RIGHT(@nodeid,3) = '9ZZ'

			set @nodeid = left(@nodeid,3) + 'A00'

		else if RIGHT(@nodeid,4) = '9ZZZ'

			set @nodeid = left(@nodeid,2) + 'A000'

		else if RIGHT(@nodeid,5) = '9ZZZZ'

			set @nodeid = left(@nodeid,1) + 'A0000'

		else if @nodeid = '9ZZZZZ'

			set @nodeid = 'A00000'

		else if Right(@nodeid,1) <> 'Z'

			set @nodeid = left(@nodeid,5) + CHAR(ASCII(right(@nodeid,1))+1) --尾數不為Z的情況下，遞增1，使用CHAR是為了字母遞增

		else if @nodeid = 'ZZZZZZ'

			raiserror('請重建編號',16,1)

		

		--尾數為Z的時候,Z前一位+1,之后的0替換

		else if RIGHT(@nodeid,5) = 'ZZZZZ'

			set @nodeid = CHAR(ASCII(@nodeid)+1) + '00000'

		else if RIGHT(@nodeid,4) = 'ZZZZ'

			set @nodeid = left(@nodeid,1) + CHAR(ASCII(right(@nodeid,5))+1) + '0000'

		else if RIGHT(@nodeid,3) = 'ZZZ'

			set @nodeid = left(@nodeid,2) + CHAR(ASCII(right(@nodeid,4))+1) + '000'

		else if RIGHT(@nodeid,2) = 'ZZ'

			set @nodeid = left(@nodeid,3) + CHAR(ASCII(right(@nodeid,3))+1) + '00'

		else if RIGHT(@nodeid,1) = 'Z'

			set @nodeid = left(@nodeid,4) + CHAR(ASCII(right(@nodeid,2))+1) + '0'

		

		--更新_sysparameters

		update _sysparameters set nodeid = @nodeid where nodetype='rowid' and nodeid = @oldnodeid

		

		if @@rowcount <> 0

			begin

			select @xxxx = @nodeid

			commit

			end

		

		else begin

			  raiserror('rowid 重復！',16,1)

			  rollback

			end



GO


    

      --uom:計量單位，uomname:計量單位名稱

CREATE TABLE guom(UOM VARCHAR(50) NOT NULL PRIMARY KEY,uomname VARCHAR(40))

INSERT INTO guom values('%','百分比')

INSERT INTO guom values('UOM','通用單位,用于無法確定單位的虛擬物料')

--數據根據實際情況設置，通用單位必須設置
    

      --設置表架構

CREATE TABLE oCompany(

	companyid orgcode NOT NULL primary key,

	companyname varchar(50),

	isproduct int, --是否在生產之中，綁定規則默認1（生產），2（停產）

	coacode float, --公司標識1 ,1.1, 1.2,便于生成父級標識碼

	rowid varchar(6) not null, --標識碼

	treecontrol varchar(60) not null --父級標識碼

)

--rowid系統中使用存儲過程來執行

insert into oCompany values('JX10','江西公司',1,1.0,'000001','000001')


    

      CREATE TABLE ms_hrcodebycompany 

(

	companyid varchar(20) not null primary key,	--公司ID

	hrbeginno int not null,		--最小工號

	hrendno int not null,		--工號上限

	cuhrcode int not null,		--當前最大的工號

	memo varchar(50)	--說明

)　


    

      CREATE     FUNCTION f_hrNextHrcode ( @companyid VARCHAR(10) )  

RETURNS @Table TABLE ( newhrid VARCHAR(10) )  

AS   

    BEGIN      

         

        INSERT  INTO @Table  

                SELECT  MAX(hrcode) + 1  

                FROM    hrgeneral a  

                        JOIN Ms_HrCodeByCompany b ON a.companyid = b.companyid  

                WHERE   a.hrcode BETWEEN b.hrbeginno AND b.hrendno  

                        AND a.companyid = @companyid   

            

        RETURN   

    END