Question

将 varchar 值 '*' 转换为数据类型为 int 的列时发生语法错误。

create table sc
(sno char(9),
cno char(4),
grade smallint,
primary key(sno,cno)
)
insert
into sc(sno,cno,grade)
values('95001','1','92')
insert
into sc(sno,cno,grade)
values('95001','2','85')
insert
into sc(sno,cno,grade)
values('95001','3','88')
insert
into sc(sno,cno,grade)
values('95002','2','90')
insert
into sc(sno,cno,grade)
values('95002','3','80')
select *from sc

建存储过程
CREATE PROCEDURE PRO_1
AS
update sc
set grade='A'
where grade>90 and grade<=100
update sc
set grade='B'
where grade>80 and grade<=90
update sc
set grade='C'
where grade>70 and grade<=80
update sc
set grade='D'
where grade>=60 and grade<=70
update sc
set grade='E'
where grade<60
运行时发生错误 --将 varchar 值 '*' 转换为数据类型为 int 的列时发生语法错误。

Answer 1

create table sc
(sno char(9),
cno char(4),
grade smallint,
primary key(sno,cno)
)
go
insert
into sc(sno,cno,grade)
values('95001','1','92')
insert
into sc(sno,cno,grade)
values('95001','2','85')
insert
into sc(sno,cno,grade)
values('95001','3','88')
insert
into sc(sno,cno,grade)
values('95002','2','90')
insert
into sc(sno,cno,grade)
values('95002','3','80')
go
select *from sc

--建存储过程
CREATE PROCEDURE PRO_1
AS
update sc
set grade='A'
where grade>90 and grade<=100
update sc
set grade='B'
where grade>80 and grade<=90
update sc
set grade='C'
where grade>70 and grade<=80
update sc
set grade='D'
where grade>=60 and grade<=70
update sc
set grade='E'
where grade<60

go
exec PRO_1
go

追问

和我写的有区别吗？

Answer 2

grade smallint类型，而在存储过程中却将grade赋为字符型，当然报错了。

追问

如何解决？这是关键 不能隐性转换吗

追答

将 grade 设为 varchar类型就可以了，而比较的话用convert（int，grade）就能转化成int型了，再将A、B、C、D 赋给grade就不会报错了