Tuesday, 11 October 2011

If exist......... else if NOT Exist

Create procedure [dbo].[PMP_CML_AddAttendeeInformationAndAddress]
 @PolarisCustomerID AS VARCHAR(50)='',
 @Customer_Id AS VARCHAR(50)='',
 @FullName AS VARCHAR(100)='',
 @Customer_Type AS VARCHAR(50)='',
 @NPINumber AS INT=0,
 @PolarisAddressID AS VARCHAR(50)='',
 @Address_ID AS VARCHAR(100)='',
 @AddressLine1 AS VARCHAR(100)='',
 @City AS VARCHAR(50)='',
 @State AS VARCHAR(50)='',
 @PostalCode AS NVARCHAR(50)='',
 @createDate AS datetime,
 @SourceSystem AS VARCHAR(100),
 @Country AS varchar(50)
AS
  BEGIN TRAN
--------RecipientType_id,State_Id,Country
DECLARE
@RecipientType_id INT,
@State_Id INT,
@COUNT INT,
@intErrorCode INT,
@AttendeeInfoAttendee_id INT

select @RecipientType_id = RecipientType_id from PMP_RecipientType where RecipientType_Name = @Customer_Type
select @State_Id = State_Id from PMP_StateMaster where State_initials = @State
 
     IF(EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
     FROM PMP_AttendeeInformation AS AI INNER JOIN
     PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
     WHERE --AI.PolarisCustomerID = @PolarisCustomerID
         AI.CustomerID = @Customer_Id
     AND AI.Fullname_organizationName = @FullName
     AND AI.RecipientType_id = @RecipientType_id
     --AND AI.NPINumber = @NPINumber
     --AND A.PolarisAddressID = @PolarisAddressID
     --AND A.Attendee_Id = @Customer_Id
     AND A.Location_id =  @Address_ID
     AND A.AddressLine1 = @AddressLine1
     AND A.City = @City
     AND A.State_Id = @State_Id
     AND A.PostalCode = @PostalCode
     AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
     AND  Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
         ))
     BEGIN
     -------No Changes in Existing records of Core Tables
     SET @COUNT =1
     END
     ELSE IF(NOT EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
     FROM PMP_AttendeeInformation AS AI INNER JOIN
     PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
     where    AI.CustomerID = @Customer_Id
     --AND AI.Fullname_organizationName = @FullName
     --AND AI.RecipientType_id = @RecipientType_id
     --AND AI.NPINumber = @NPINumber
     --AND A.PolarisAddressID = @PolarisAddressID
     --AND A.Attendee_Id = @Customer_Id
       AND A.Location_id =  @Address_ID
     --AND A.AddressLine1 = @AddressLine1
     --AND A.City = @City
     --AND A.State_Id = @State_Id
     --AND A.PostalCode = @PostalCode
  AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
  AND  Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
     ))
    
     BEGIN
    
    
     INSERT INTO PMP_AttendeeInformation(PolarisCustomerID,CustomerID,Fullname_organizationName,RecipientType_id,NPINumber,Isorg,CreatedDate,Creator)
     VALUES(@PolarisCustomerID,@Customer_Id,@FullName,@RecipientType_id,@NPINumber,1,@createDate,@SourceSystem)
     select  @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation --where  CustomerID = @Customer_Id and  Fullname_organizationName = @FullName
     INSERT INTO PMP_Address(PolarisAddressID,Attendee_Id,Location_id,AddressLine1,City,State_Id,PostalCode,Country,CreateDate,Creator)
     VALUES(@PolarisAddressID,@AttendeeInfoAttendee_id,@Address_ID,@AddressLine1,@City,@State_Id,@PostalCode,@Country,@createDate,@SourceSystem)
    
     END
    
     ELSE
    
     BEGIN
   ------------------------------ Update Record ---------------------------------------
    
     select  @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation
    
 UPDATE PMP_AttendeeInformation SET
     PolarisCustomerID = @PolarisCustomerID
     ,Fullname_organizationName = @FullName
     ,RecipientType_id = @RecipientType_id
     ,NPINumber = @NPINumber
  ,Creator = @SourceSystem
     WHERE Attendee_Id  = @AttendeeInfoAttendee_id
   
    UPDATE PMP_Address SET
     PolarisAddressID = @PolarisAddressID
     ,Location_id = @Address_ID
     ,AddressLine1 = @AddressLine1
     ,City = @City
     ,State_Id = @State_Id
     ,PostalCode = @PostalCode
     ,Country = @Country
  ,Creator = @SourceSystem
     WHERE Attendee_Id = @AttendeeInfoAttendee_id
   
     END
    
     SELECT @intErrorCode = @@ERROR
 IF (@intErrorCode <> 0) GOTO PROBLEM
 COMMIT TRAN
    ----------------------------------------
   PROBLEM:
 IF (@intErrorCode <> 0)
 BEGIN
     ---PRINT 'Unexpected error occurred!'
   ROLLBACK TRAN
    END

No comments:

Post a Comment