我有三个表,格式都一样,表名为userinfo,userinfo1,userinfo2,现在要有一个存储过程,讲userinfo2表的数据插入到userinfo或userinfo1中,其中有个判断,是插入userinfo还是userinfo1,同时产出被插入表中原有的插入值的name行
我写了一个,但是没有判断
ALTER PROCEDURE [dbo].[Procedure_Name1]
-- Add the parameters for the stored procedure here
@name varchar(50)AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;begin tran
delete from userinfo where [name]=@name
if (@@error<>0)
goto errExit insert into userinfo([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
if (@@error<>0)
goto errExit
commit tran
return 0
errExit:
rollback tran
return 1
END
我写了一个,但是没有判断
ALTER PROCEDURE [dbo].[Procedure_Name1]
-- Add the parameters for the stored procedure here
@name varchar(50)AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;begin tran
delete from userinfo where [name]=@name
if (@@error<>0)
goto errExit insert into userinfo([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
if (@@error<>0)
goto errExit
commit tran
return 0
errExit:
rollback tran
return 1
END
insert into userinfo1
select * from userinfo where 插入到userinfo1的条件insert into userinfo2
select * from userinfo where 插入到userinfo2的条件delete userinfo where name=@namecommit
你根据什么条件来判断到底是insert到哪个table啊?IF (条件成立)
insert into userinfo
select * from userinfo2
where ....
ELSE
insert into userinfo1
select * from userinfo2
where ....
同时产出被插入表中原有的插入值的name行
>>
这个在插入前select出来就好了
急
@flag int,
@name varchar(50) AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; begin tran if @flag = 1
begin delete from userinfo where [name]=@name
if (@@error <>0)
goto errExit insert into userinfo([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
if (@@error <>0)
goto errExit
endif @flag = 0
begin delete from userinfo1 where [name]=@name
if (@@error <>0)
goto errExit insert into userinfo1([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
if (@@error <>0)
goto errExit
end
commit tran
return 0
errExit:
rollback tran
return 1
END
-- Add the parameters for the stored procedure here
@name varchar(50),
@flag bit =1 AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;begin tran
if @flag=1
begin
select * from userinfo where [name]=@name
delete from userinfo where [name]=@name
if (@@error <>0)
goto errExit
insert into userinfo([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
if (@@error <>0)
goto errExit
end
else
begin
select * from userinfo1 where [name]=@name
delete from userinfo1 where [name]=@name insert into userinfo1([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
if (@@error <>0)
goto errExitend
commit tran
return 0
errExit:
rollback tran
return 1
END
if @flag=1
begin
delete userinfo where name=@name
insert into userinfo([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
end
else
begin
delete userinfo1 where name=@name
insert into userinfo1([name],sex,age,birthdate)
select [name],sex,age,birthdate from userinfo2 where [name]=@name
end
@flag bit=0
)
asset nocount on
begin transif @flag = 1
begin
delete from userinfo where exists(
select 1 from userinfo2 where [name]=userinfo2.[name])
if @@error<>0 goto roll insert into userinfo
select * from userinfo2
if @@error<>0 goto rollend
else if @flag = 0
begin
delete from userinfo1 where exists(
select 1 from userinfo2 where [name]=userinfo2.[name])
if @@error<>0 goto roll insert into userinfo1
select * from userinfo2
if @@error<>0 goto rollendif @@trancount>0 commit tranret:
returnroll:
if @@trancount>0 rollback tran
goto ret