下面是我写一个过程 插入三个表中 我用到事务 大家看下我这样写有问题没 在性能和速度上是否需要修改
大家有什么好办法或建议没
ALTER proc [dbo].[pr_insertfwbuy](
@fm_title nvarchar(50),
@fm_sf int,
@fm_qy int,
@fm_dq int,
@fm_type int,
@fm_source int,
@fm_fwlx int,
@fm_price decimal(18, 0),
@fm_fwsm nvarchar(300),
@fm_mj int,
@fm_address nvarchar(50),
@fm_adduser int,
@u_name nvarchar(10),
@u_phone char(12),
@u_email char(30),
@u_qqmsn char(30),
@p_pathslt0 char(50),
@p_path0 char(50),
@p_pathslt1 char(50),
@p_path1 char(50),
@p_pathslt2 char(50),
@p_path2 char(50))
as
declare @fmid int;
begin tran--使用事务
--插入第一个表
insert into T_fwbuy(fm_title,fm_sf,fm_qy,fm_dq,fm_type,fm_source,fm_fwlx,fm_price,fm_fwsm,fm_mj,fm_address,fm_addtime,fm_endtime,fm_adduser,fm_enduser,fm_sfjs)
values(@fm_title,@fm_sf,@fm_qy,@fm_dq,@fm_type,@fm_source,@fm_fwlx,@fm_price,@fm_fwsm,@fm_mj,@fm_address,getdate(),getdate(),@fm_adduser,@fm_adduser,0)
select @fmid=@@identity;
--插入第二个表
insert into T_fwmmuser(u_newid,u_type,u_name,u_phone,u_email,u_qqmsn,u_addtime)
values(@fmid,@fm_type,@u_name,@u_phone,@u_email,@u_qqmsn,getdate());declare @i int;
set @i=0
while @i<3
begin
if '@p_path'+@i!=null and '@p_path'+@i!=''
begin
--插入第三个表
insert into T_photo(P_type,p_newid,p_pathslt,p_path)
values(@fm_type,@fmid,'@p_pathslt'+@i,'@p_path'+@i)
end
set @i=@i+1;
end
rollback tran
大家有什么好办法或建议没
ALTER proc [dbo].[pr_insertfwbuy](
@fm_title nvarchar(50),
@fm_sf int,
@fm_qy int,
@fm_dq int,
@fm_type int,
@fm_source int,
@fm_fwlx int,
@fm_price decimal(18, 0),
@fm_fwsm nvarchar(300),
@fm_mj int,
@fm_address nvarchar(50),
@fm_adduser int,
@u_name nvarchar(10),
@u_phone char(12),
@u_email char(30),
@u_qqmsn char(30),
@p_pathslt0 char(50),
@p_path0 char(50),
@p_pathslt1 char(50),
@p_path1 char(50),
@p_pathslt2 char(50),
@p_path2 char(50))
as
declare @fmid int;
begin tran--使用事务
--插入第一个表
insert into T_fwbuy(fm_title,fm_sf,fm_qy,fm_dq,fm_type,fm_source,fm_fwlx,fm_price,fm_fwsm,fm_mj,fm_address,fm_addtime,fm_endtime,fm_adduser,fm_enduser,fm_sfjs)
values(@fm_title,@fm_sf,@fm_qy,@fm_dq,@fm_type,@fm_source,@fm_fwlx,@fm_price,@fm_fwsm,@fm_mj,@fm_address,getdate(),getdate(),@fm_adduser,@fm_adduser,0)
select @fmid=@@identity;
--插入第二个表
insert into T_fwmmuser(u_newid,u_type,u_name,u_phone,u_email,u_qqmsn,u_addtime)
values(@fmid,@fm_type,@u_name,@u_phone,@u_email,@u_qqmsn,getdate());declare @i int;
set @i=0
while @i<3
begin
if '@p_path'+@i!=null and '@p_path'+@i!=''
begin
--插入第三个表
insert into T_photo(P_type,p_newid,p_pathslt,p_path)
values(@fm_type,@fmid,'@p_pathslt'+@i,'@p_path'+@i)
end
set @i=@i+1;
end
rollback tran
how can you commit the data?
set @i=0
while @i <3
begin
if '@p_path'+@i!=null and '@p_path'+@i!='' 这句是不是有些多了。
@i>=0的情况下,这个判断条件始终成立的。
你的数据量大吗?
好像插入的时候没必要这样做。插入的时候判断一下@@error就好了。
只有回滚没有提交动作?
@userId int,
@privateMessageId int
as
begin
if(@userId >0 and @privateMessageId >0)
begin
BEGIN TRANSACTION Tran_MSG
BEGIN
delete from user_to_p_msg
where user_id = @userId
and private_message_id = @privateMessageId
delete from P_MSG where user_id=@userId and private_message_id =@privateMessageId
END IF (@@ERROR<>0)
ROLLBACK TRANSACTION Tran_MSG
ELSE
COMMIT TRANSACTION Tran_MSG
end
end
@id int,
@name varchar(50)
as
declare @count int
begin
if(@id>0 and @name !='')
begin
begin transaction tran_msginsert into stu1 values(@id,@name)end
set @count=@@error
print @count
endexec stup 2,'jack'
这个是我写的当中有点错误不过定义declare @count int
是对的。
create proc stup
@id int,
@name varchar(50)
as
declare @count int
begin
if(@id>0 and @name !='')
begin
begin transaction tran_msg insert into stu1 values(@id,@name) end
set @count=@@error
print @count
end exec stup 2,'jack'
是有错误的我只是想告诉你应该定义一个变量。。不足之出还请见谅