在数据库中，某些表的字段很长，而且是自增的，但是由于有前缀，不能直接使用INT类型。
我们可以通过编写存储过程来实现自动生成Id的功能：
一、首先建立数据库和表，并向其中插入一条数据：
1 Use Master
2 GO
3
4 -- =============================================
5 -- Basic Create Database Template
6 -- =============================================
7 IF EXISTS (SELECT *
8 FROM master..sysdatabases
9 WHERE name = N'test_db')
10 DROP DATABASE test_db
11 GO
12
13 CREATE DATABASE test_db
14 GO
15
16
17 Use test_db;
18 GO
19
20 -- =============================================
21 -- Create table basic template
22 -- =============================================
23 IF EXISTS(SELECT name
24 FROM sysobjects
25 WHERE name = N'T_User'
26 AND type = 'U')
27 DROP TABLE test_table
28 GO
29
30 CREATE TABLE T_User (
31 userId VARCHAR(50) PRIMARY KEY,
32 userName VARCHAR(50))
33 GO
34
35 INSERT INTO T_User VALUES('User0001', 'xulin')
36 GO
二、编写自动生成Id的存储过程：
1 IF EXISTS (SELECT name
2 FROM sysobjects
3 WHERE name = N'proc_getNewID'
4 AND type = 'P')
5 DROP PROCEDURE proc_getNewID
6 GO
7
8 CREATE PROCEDURE proc_getNewID
9 @maxId VARCHAR(20), -- 传人的最大Id值
10 @prefix VARCHAR(8), -- 前缀
11 @suffix_len INT, -- 后缀长度
12 @newId VARCHAR(20) output -- 输出参数
13 AS
14 DECLARE @num varchar(20)
15 SET @num = right(@maxID,@suffix_len) -- 得到后缀
16
17 IF (@num IS null) -- 如果没有传入MaxId，则生成第一个newId
18 BEGIN
19 SET @newID = @prefix + REPLICATE('0',@suffix_len-1)+'1'
20 -- REPLICATE()以指定的次数重复字符表达式。然后加1
21 END
22 ELSE
23 BEGIN
24 SET @num = @num + 1
25 SET @newID = @prefix + REPLICATE('0', @suffix_len-len(@num)) + @num
26 END
27 RETURN
28
29 go
编写思路：
通过接收三个参数来生成新的Id，传入参数为：最大ID， 前缀， 后缀大小。
如果输入的ID为空，这生成第一个ID：
1 -- 测试代码：
2 declare @num varchar(20)
3 exec proc_getNewID null,'PERM',4,@num output
4 select @num
5 -- 输出：PERM0001
6
如果输入不为空，这生成最大Id的后一个Id：
1 -- 测试代码：
2 declare @num varchar(20)
3 exec proc_getNewID 'PERM000011', 'PERM', 6, @num output
4 select @num
5
6 --输出：PERM000012
7
三、在某张表上应用该存储过程：
1 -- =============================================
2 -- Create procedure insertAssetBudgetState
3 -- 创建 插入用户信息 的存储过程
4 -- =============================================
5 IF EXISTS (SELECT name
6 FROM sysobjects
7 WHERE name = N'proc_insertUser'
8 AND type = 'P')
9 DROP PROCEDURE proc_insertUser
10 GO
11
12 CREATE PROCEDURE proc_insertUser @userName varchar(20)
13 AS
14 IF exists (select * from T_User
15 where UserName=@userName)
16 return 1
17 ELSE
18 declare @maxID varchar(20)
19 declare @newId varchar(20)
20 select @maxId=Max(UserId) from T_User
21 exec proc_getNewID @maxId, 'User', 4, @newId OUTPUT
22 -- 最大ID， 前缀， 后缀大小， 新的ID（输出）
23 INSERT into T_User values(@newId, @userName)
24 return 0
25
26 GO
27
28 EXEC proc_insertUser 'zhangke'
29 EXEC proc_insertUser 'chenwenbin'
30 EXEC proc_insertUser 'wangchao'
31 EXEC proc_insertUser 'yangyan'
32
33 SELECT * FROM T_User
输出结果：
四、数据类型的自动转换：
在编写自动生成Id的存储过程中使用了自动转换：@num=@num+1，这里的@nun为varchar，最后得到int型。
Transact-SQL 参考：
1 +（加）
2 两个数相加。这个加法算术运算符也可以将一个以天为单位的数字加到日期中。
3
4 语法
5 expression + expression
6
7 参数
8 expression
9
10 是数字分类中任何数据类型（bit 数据类型除外）的任何有效 Microsoft® SQL Server™ 表达式。
11
12 结果类型
13 返回优先级较高的参数的数据类型。有关更多信息，请参见数据类型的优先顺序。
14
1 C. 将字符和整型数据类型相加
2 本示例通过将字符数据类型转换为 int，将 int 数据类型值与字符值相加。如果在 char 字符串中有无效的字符，则 SQL Server 将返回错误。
3
4 DECLARE @addvalue int
5 SET @addvalue = 15
6 SELECT '125127' + @addvalue
7
8 下面是结果集：
9
10 -----------------------
11 125142
12
13 (1 row(s) affected)
14
15
1 数据类型的优先顺序
2 当两个不同数据类型的表达式用运算符组合后，数据类型的优先顺序规则确定哪种数据类型要向另一种转换。优先顺序低的数据类型向优先顺序高的数据类型转换。如果此转换不是所支持的固有转换，则返回错误。当两个操作数表达式有相同的数据类型时，运算的结果就为那种数据类型。
3
4 下面是 Microsoft® SQL Server™ 2000 数据类型的优先顺序：
5
6 sql_variant（最高）
7
8
9 datetime
10
11
12 smalldatetime
13
14
15 float
16
17
18 real
19
20
21 decimal
22
23
24 money
25
26
27 smallmoney
28
29
30 bigint
31
32
33 int
34
35
36 smallint
37
38
39 tinyint
40
41
42 bit
43
44
45 ntext
46
47
48 text
49
50
51 image
52
53
54 timestamp
55
56
57 uniqueidentifier
58
59
60 nvarchar
61
62
63 nchar
64
65
66 varchar
67
68
69 char
70
71
72 varbinary
73
74
75 binary（最低）
76
在数据类型的优先顺序中，int 比 varchar 高，计算时回得到int结果。
posted on 2006-09-26 11:39
CoderDream 阅读(1418)
评论(0) 编辑 收藏 所属分类:
数据库