自定义函数
--建立函数
Create Function F_GetCustomerID()
Returns Varchar(16)
As
Begin
Declare @S Varchar(16)
Select @S= 'CR'+Right(100000000000000+IsNull(Right(Max(CustomerID),14),0)+1,14) From CustomerInfomation
Return @S
End
GO
--建立测试环境
CREATE TABLE CustomerInfomation(
CustomerID varchar(16) NOT NULL Default dbo.F_GetCustomerID(),
CustomerName varchar(20) NOT NULL,
Constraint PK_Customer_ID Primary key(CustomerID)
)
GO
--测试
Insert CustomerInfomation(CustomerName ) values('aa')
Insert CustomerInfomation(CustomerName ) values( 'bb')
Insert CustomerInfomation(CustomerName ) values( 'cc' )
Select * From CustomerInfomation
GO
--删除测试环境
Drop Table CustomerInfomation
Drop Function F_GetCustomerID
--結果
/*
CustomerID CustomerName
CR00000000000001 aa
C00000000000002 bb
BK00000000000003 cc
*/