哪位大神帮忙看看我的存储过程有什么问题
具体实现:根据传进来的type来判断,如果type<>0,将backChecked置为0,如果type=0,将backChecked的值置为id
请问下面的存储过程有什么问题,我执行的时候数据库里的数据没有发生变化
USE [YFTrade]
GO
/****** Object: StoredProcedure [dbo].[dispatch_message_Checked] Script Date: 03/11/2014 15:55:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[dispatch_message_Checked]
-- Add the parameters for the stored procedure here
@Output int output,
@ID varchar(100) ,
@type varchar(100),
@oper_ID varchar(100),
@operator varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
begin TRANSACTION
declare @rtn int
set @rtn = 0
-- Insert statements for procedure here
Begin
Insert into [dbo].[D-BFPC-Dispatch-His]
select *,@Operator,GETDATE(),1 from [dbo].[D-BFPC-Dispatch] where [ID] = @ID
end
If(@type <> '0')
Begin
Update [dbo].[D-BFPC-Dispatch]
Set [backChecked] =0,
[back_date]=GETDATE()
Where [ID]=@ID
if(@@error <> 0) or (@@ROWCOUNT <> 1)
begin
set @rtn = 2
goto Error
end
end
else
begin
Update [dbo].[D-BFPC-Dispatch]
Set [backChecked]=@ID,
[back_date]=GETDATE()
Where [ID]=@ID
if(@@error <> 0) or (@@ROWCOUNT <> 1)
begin
set @rtn = 2
goto Error
end
end
COMMIT TRANSACTION
set @Output = @rtn
RETURN @rtn
Error:
ROLLBACK TRANSACTION
set @Output = @rtn
RETURN @rtn
END
------解决方案--------------------这样试试
ALTER PROCEDURE [dbo].[dispatch_message_Checked]
-- Add the parameters for the stored procedure here
@Output INT OUTPUT ,
@ID VARCHAR(100) ,
@type VARCHAR(100) ,
@oper_ID VARCHAR(100) ,
@operator VARCHAR(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @rtn INT
SET @rtn = 0
-- Insert statements for procedure here
BEGIN
&n