SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER procedure sp_add_card( @card_soleId varchar(50), --@number_of_card int, @card_type char(1), @create_time datetime, @last_time datetime, @end_time datetime, @operator varchar(30) ) as declare @cardno varchar(50) declare @cardpassword varchar(16) declare @cardmoney numeric(7,2) --declare begin transaction begin if exists(select AcctSoleID from acct_soleId where cast(AcctSoleID as varchar(50))=@card_soleId ) begin rollback return 2 end else begin select @cardno=acct_acct_no,@cardpassword=acct_password,@cardmoney=acct_money from temp_acct where cast(acct_identity as varchar(50))=@card_soleId insert into acct_info(acctNo,acctType,acctPass,preMoney,curMoney,createDate,lastDate,endDate,czy) values (@cardno,@card_type,@cardpassword,@cardmoney,@cardmoney,@create_time,@last_time,@end_time,@operator) insert into acct_soleId(AcctSoleID,AcctNo)values(@card_soleId,@cardno) end end if @@error<>0 begin rollback return 1 --操作失败 end else begin commit return 0 --操作成功 end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO这个是实际的
ALTER procedure sp_add_card( @card_soleId varchar(50), --@number_of_card int, @card_type char(1), @create_time datetime, @last_time datetime, @end_time datetime, @operator varchar(30) ) as declare @cardno varchar(50) declare @cardpassword varchar(16) declare @cardmoney numeric(7,2) --declare begin transaction begin if exists(select AcctSoleID from acct_soleId where cast(AcctSoleID as varchar(50))=@card_soleId ) begin rollback return 2 end else begin select @cardno=acct_acct_no,@cardpassword=acct_password,@cardmoney=acct_money from temp_acct where cast(acct_identity as varchar(50))=@card_soleId insert into acct_info(acctNo,acctType,acctPass,preMoney,curMoney,createDate,lastDate,endDate,czy) values (@cardno,@card_type,@cardpassword,@cardmoney,@cardmoney,@create_time,@last_time,@end_time,@operator)
if @cardno is null print '不能插入NULL值' else insert into acct_soleId(AcctSoleID,curMoney) values(@card_soleId,@cardno,0) end end if @@error<>0 begin rollback return 1 --操作失败 end else begin commit return 0 --操作成功 end
select AcctSoleID from acct_soleId where cast(AcctSoleID as varchar(50))=@card_soleId
zjcxc(邹建)
表:acct_soleId 字段:AcctSoleID
类型varchar(50)表:temp_acct 字段:acct_identity
varchar(50)
那查询中这样的条件已经是没有意义的了.所以楼主还是要根据实现情况来改表结构或存储过程.
这种方法以后出现这种错误
服务器: 消息 515,级别 16,状态 2,过程 sp_add_card,行 27
无法将 NULL 值插入列 'curMoney',表 'bill.dbo.acct_info';该列不允许空值。INSERT 失败。
语句已终止。
服务器: 消息 515,级别 16,状态 2,过程 sp_add_card,行 28
无法将 NULL 值插入列 'AcctNo',表 'bill.dbo.acct_soleID';该列不允许空值。INSERT 失败。
语句已终止。
存储过程: bill.dbo.sp_add_card
返回代码 = 1
GO
SET ANSI_NULLS ON
GO
ALTER procedure sp_add_card(
@card_soleId varchar(50),
--@number_of_card int,
@card_type char(1),
@create_time datetime,
@last_time datetime,
@end_time datetime,
@operator varchar(30)
)
as
declare @cardno varchar(50)
declare @cardpassword varchar(16)
declare @cardmoney numeric(7,2)
--declare begin transaction
begin
if exists(select AcctSoleID from acct_soleId where cast(AcctSoleID as varchar(50))=@card_soleId )
begin
rollback
return 2
end
else
begin
select @cardno=acct_acct_no,@cardpassword=acct_password,@cardmoney=acct_money from temp_acct where cast(acct_identity as varchar(50))=@card_soleId
insert into acct_info(acctNo,acctType,acctPass,preMoney,curMoney,createDate,lastDate,endDate,czy) values (@cardno,@card_type,@cardpassword,@cardmoney,@cardmoney,@create_time,@last_time,@end_time,@operator)
insert into acct_soleId(AcctSoleID,AcctNo)values(@card_soleId,@cardno)
end
end if @@error<>0
begin
rollback
return 1 --操作失败
end
else
begin
commit
return 0 --操作成功
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO这个是实际的
acct_info(acctNo,acctType,acctPass,preMoney,curMoney,createDate,lastDate,endDate,czy) acct_soleId(AcctSoleID,AcctNo)
没有int型的
@card_soleId varchar(50),
--@number_of_card int,
@card_type char(1),
@create_time datetime,
@last_time datetime,
@end_time datetime,
@operator varchar(30)
)
as
declare @cardno varchar(50)
declare @cardpassword varchar(16)
declare @cardmoney numeric(7,2)
--declare begin transaction
begin
if exists(select AcctSoleID from acct_soleId where cast(AcctSoleID as varchar(50))=@card_soleId )
begin
rollback
return 2
end
else
begin
select @cardno=acct_acct_no,@cardpassword=acct_password,@cardmoney=acct_money from temp_acct where cast(acct_identity as varchar(50))=@card_soleId
insert into acct_info(acctNo,acctType,acctPass,preMoney,curMoney,createDate,lastDate,endDate,czy) values (@cardno,@card_type,@cardpassword,@cardmoney,@cardmoney,@create_time,@last_time,@end_time,@operator)
if @cardno is null
print '不能插入NULL值'
else
insert into acct_soleId(AcctSoleID,curMoney)
values(@card_soleId,@cardno,0)
end
end if @@error<>0
begin
rollback
return 1 --操作失败
end
else
begin
commit
return 0 --操作成功
end
呵呵acct_identity 是int型
谢谢各位高手
马上给分
你们真的太热新了