USE [XiaoBo]
GO
/****** 对象: StoredProcedure [dbo].[Products_IU] 脚本日期: 07/06/2007 09:51:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Products_IU]
(
@Pid int = NULL OUTPUT,
@PPid int,
@PTid int,
@FTid int,
@Personid int,
@PBarCode nvarchar(50) = NULL,
@ProdNO nvarchar(50) = NULL,
@Name nvarchar(50) = NULL,
@Summary nvarchar(255) = NULL,
@PBody ntext = NULL,
@status int = NULL,
@InputDate datetime = NULL,
@ModifyDate datetime = NULL ,
@ActType INT ,
@RetValue INT output
)
AS
DECLARE @ID INT
DECLARE @ERROR INT
DECLARE @ROWCOUNT INT
-- CREATE
IF @ActType = 0
BEGIN
INSERT INTO [Products]
(
[PPid],
[PTid],
[FTid],
[Personid],
[PBarCode],
[ProdNO],
[Name],
[Summary],
[PBody],
[status],
[InputDate],
[ModifyDate]
)
VALUES
(
@PPid,
@PTid,
@FTid,
@Personid,
@PBarCode,
@ProdNO,
@Name,
@Summary,
@PBody,
@status,
@InputDate,
@ModifyDate
)
SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
IF( @ERROR <> 0 )
BEGIN
RAISERROR( 'An error occurred while trying to create a new ucds_Society record.', 16, 1 )
Set @RetValue= -1
END
ELSE
BEGIN
Set @RetValue= @ID
END
END
ELSE IF( @ActType = 1 )
BEGIN
-- UPDATE
IF NOT EXISTS( SELECT * FROM [Products] WHERE [Pid] = @Pid )
Set @RetValue=-1
ELSE
BEGIN
UPDATE [Products]
SET
[PPid] = @PPid,
[PTid] = @PTid,
[FTid] = @FTid,
[Personid] = @Personid,
[PBarCode] = @PBarCode,
[ProdNO] = @ProdNO,
[Name] = @Name,
[Summary] = @Summary,
[PBody] = @PBody,
[status] = @status,
[InputDate] = @InputDate,
[ModifyDate] = @ModifyDate
WHERE
[Pid] = @Pid
SELECT @ERROR = @@ERROR, @ID = @@IDENTITY, @ROWCOUNT = @@ROWCOUNT
IF( @ERROR <> 0 )
BEGIN
RAISERROR('Could not update the Products record . ', 16, 1 )
Set @RetValue=-1
END
ELSE
BEGIN
Set @RetValue=2
END
END
END
ELSE
BEGIN
RAISERROR( 'The ActionType value should be between 0 and 1. The value of %d was not within this range', 16, 1, @ActType )
Set @RetValue=-1
END