存储过程是
create PROCEDURE tr_jifenhlp_pro
(
@cd char(8) ,
@sh decimal(15,2),
@ky char(6),
@bz char(50)
)
as if not exists
(select 1 from
(select (sum(kouhj)-abs(@sh)*50) as shishje
from tr_jifen a
where cardno=@cd
and xulh<>'非本日小票'
and a.shishje>0) c) begin select '此卡积分不足,不予兑换' end
elsebegin insert into tr_jifen
(cardno,xulh,riqi,shishje,kouhj,jiqrq,shoukyh,jfsky,ontime,beizhu)
select @cd,'兑换','','0','abs(@sh)*(-50)',convert(char(10),getdate(),120),'',@ky,convert(char(10),getdate(),8),@bz select top 10 cardno,xulh,riqi,shishje,kouhj as 积分换取金额,jiqrq,shoukyh,jfsky,ontime,beizhu
from tr_jifen
where jfsky=@ky
and cardno=@cd
order by ontime descend
go提示
服务器: 消息 8114,级别 16,状态 5,过程 tr_jifenhlp_pro,行 30
Error converting data type varchar to numeric.
所用到的表结构create table tr_customer --创建新tr_customer表
(
xh int identity(1,1),
cardno char(8) not null,
certif_no char(18) not null,
certif char(8) not null,
uname char(8) not null,
sex char(2) null,
phone char(12) null,
fkrq char(10) null
--primary key (certif_no)设置主键不运行
)
-----------------------------------------------create table tr_jifen --创建新tr_jifen表
(
cardno char(8) not null,
xulh char(12) not null,
riqi char(10) null,
shishje decimal(15,2) null default 0,
kouhj decimal (15,2) null default 0,
jiqrq char(10) null,
shoukyh char(6) null,
jfsky char(6) null,
ontime char(10) null,
beizhu char(50 null
)麻烦帮看一下,非常感谢
create PROCEDURE tr_jifenhlp_pro
(
@cd char(8) ,
@sh decimal(15,2),
@ky char(6),
@bz char(50)
)
as if not exists
(select 1 from
(select (sum(kouhj)-abs(@sh)*50) as shishje
from tr_jifen a
where cardno=@cd
and xulh<>'非本日小票'
and a.shishje>0) c) begin select '此卡积分不足,不予兑换' end
elsebegin insert into tr_jifen
(cardno,xulh,riqi,shishje,kouhj,jiqrq,shoukyh,jfsky,ontime,beizhu)
select @cd,'兑换','','0','abs(@sh)*(-50)',convert(char(10),getdate(),120),'',@ky,convert(char(10),getdate(),8),@bz select top 10 cardno,xulh,riqi,shishje,kouhj as 积分换取金额,jiqrq,shoukyh,jfsky,ontime,beizhu
from tr_jifen
where jfsky=@ky
and cardno=@cd
order by ontime descend
go提示
服务器: 消息 8114,级别 16,状态 5,过程 tr_jifenhlp_pro,行 30
Error converting data type varchar to numeric.
所用到的表结构create table tr_customer --创建新tr_customer表
(
xh int identity(1,1),
cardno char(8) not null,
certif_no char(18) not null,
certif char(8) not null,
uname char(8) not null,
sex char(2) null,
phone char(12) null,
fkrq char(10) null
--primary key (certif_no)设置主键不运行
)
-----------------------------------------------create table tr_jifen --创建新tr_jifen表
(
cardno char(8) not null,
xulh char(12) not null,
riqi char(10) null,
shishje decimal(15,2) null default 0,
kouhj decimal (15,2) null default 0,
jiqrq char(10) null,
shoukyh char(6) null,
jfsky char(6) null,
ontime char(10) null,
beizhu char(50 null
)麻烦帮看一下,非常感谢
(
@cd char(8) ,
@sh decimal(15,2),
@ky char(6),
@bz char(50)
)
as if not exists
(select 1 from
(select (sum(kouhj)-abs(@sh)*50) as shishje
from tr_jifen a
where cardno=@cd
and xulh<>'非本日小票'
and a.shishje>0) c) begin select '此卡积分不足,不予兑换' end
elsebegin insert into tr_jifen
(cardno,xulh,riqi,shishje,kouhj,jiqrq,shoukyh,jfsky,ontime,beizhu)
select @cd,'兑换','','0',abs(@sh)*(-50),convert(char(10),getdate(),120),'',@ky,convert(char(10),getdate(),8),@bz select top 10 cardno,xulh,riqi,shishje,kouhj as 积分换取金额,jiqrq,shoukyh,jfsky,ontime,beizhu
from tr_jifen
where jfsky=@ky
and cardno=@cd
order by ontime descend
go
create PROCEDURE tr_jifenhlp_pro
(
@cd char(8) ,
@sh decimal(15,2),
@ky char(6),
@bz char(50)
)
as if not exists
(select 1 from
(select (sum(kouhj)-abs(@sh)*50) as shishje
from tr_jifen a
where cardno=@cd
and xulh<>'非本日小票'
and a.shishje>0) c)
begin
select '此卡积分不足,不予兑换'
end
else
begin
insert into tr_jifen
(cardno,xulh,riqi,shishje,kouhj,jiqrq,shoukyh,jfsky,ontime,beizhu)
select @cd,'兑换','',0,abs(@sh)*(-50),convert(char(10),getdate(),120),'',@ky,convert(char(10),getdate(),8),@bz select top 10 cardno,xulh,riqi,shishje,kouhj as 积分换取金额,jiqrq,shoukyh,jfsky,ontime,beizhu
from tr_jifen
where jfsky=@ky
and cardno=@cd
order by ontime descend
go
insert into tr_jifen
(cardno,xulh,riqi,shishje,kouhj,jiqrq,shoukyh,jfsky,ontime,beizhu)
select @cd,'兑换','','0','abs(@sh)*(-50)',convert(char(10),getdate(),120),'',@ky,convert(char(10),getdate(),8),@bz
............其中'abs(@sh)*(-50)'两边的双引号去掉就可以了~