加入一个go: CREATE PROCEDURE 编学号 AS ALTER TABLE 学生学籍预录表 ADD [ID] [int] IDENTITY (1, 1) NOT NULL go update 学生学籍预录表 set 部门=(select 部门名称 from 部门专业对应表 where ltrim(rtrim(部门专业对应表.校内专业名称))=ltrim(rtrim(学生学籍预录表.专业))) declare @bumen varchar(20),@renshu int,@i int,@bumendaima char(2),@xuhao char(3),@ID int declare bumen_cursor cursor for SELECT 部门, COUNT(*) AS Expr1 FROM 学生学籍预录表 GROUP BY 部门 open bumen_cursor fetch next from bumen_cursor into @bumen,@renshu while @@fetch_status=0 begin select @bumendaima=部门代码 from 部门专业对应表 where 部门名称=@bumen declare yulu_cursor cursor for select [ID] from 学生学籍预录表 where 部门=@bumen open yulu_cursor fetch next from yulu_cursor into @ID set @i=1 while @@fetch_status=0 begin if @i<10 set @xuhao='00'+CONVERT(char(1),@i) else if @i<100 set @xuhao='0'+ CONVERT(char(2),@i) else if @i<1000 set @xuhao= CONVERT(char(3),@i) update 学生学籍预录表 set 学号='498'+@bumendaima+@xuhao where ID=@ID fetch next from yulu_cursor into @ID set @i=@i+1 end close yulu_cursor deallocate yulu_cursor fetch next from bumen_cursor into @bumen,@renshu end close bumen_cursor deallocate bumen_cursorALTER TABLE 学生学籍预录表 DROP COLUMN [ID]
CREATE PROCEDURE 编学号 AS
ALTER TABLE 学生学籍预录表 ADD [ID] [int] IDENTITY (1, 1) NOT NULL
go
update 学生学籍预录表 set 部门=(select 部门名称 from 部门专业对应表 where ltrim(rtrim(部门专业对应表.校内专业名称))=ltrim(rtrim(学生学籍预录表.专业)))
declare @bumen varchar(20),@renshu int,@i int,@bumendaima char(2),@xuhao char(3),@ID int
declare bumen_cursor cursor for SELECT 部门, COUNT(*) AS Expr1 FROM 学生学籍预录表 GROUP BY 部门
open bumen_cursor
fetch next from bumen_cursor into @bumen,@renshu
while @@fetch_status=0
begin
select @bumendaima=部门代码 from 部门专业对应表 where 部门名称=@bumen
declare yulu_cursor cursor for select [ID] from 学生学籍预录表 where 部门=@bumen
open yulu_cursor
fetch next from yulu_cursor into @ID
set @i=1
while @@fetch_status=0
begin
if @i<10
set @xuhao='00'+CONVERT(char(1),@i)
else if @i<100
set @xuhao='0'+ CONVERT(char(2),@i)
else if @i<1000
set @xuhao= CONVERT(char(3),@i)
update 学生学籍预录表 set 学号='498'+@bumendaima+@xuhao where ID=@ID
fetch next from yulu_cursor into @ID
set @i=@i+1
end
close yulu_cursor
deallocate yulu_cursor
fetch next from bumen_cursor into @bumen,@renshu
end
close bumen_cursor
deallocate bumen_cursorALTER TABLE 学生学籍预录表 DROP COLUMN [ID]
时,不应用not null,因为加入一个字段时,它都为空
没有错,因为有IDENTITY (1, 1)
可以骗过SQLSERVER的语法检查!
rockynmc(rockynmc) 我不知你讲的是什么意思.能再解释吗