Products_IU

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