vbs script open window using url and close

set WshShell = WScript.CreateObject("WScript.Shell")
set shell = CreateObject("Shell.Application")
shell.Open "http://www.google.com"
WScript.Sleep 5000
WshShell.SendKeys"^{F4}"

If exist......... else if NOT Exist SSIS procedure

CREATE PROCEDURE [dbo].[AddEventDetailsFromStaging]
@ExpenseId AS VARCHAR(50)='',
@Expense_Nature AS VARCHAR(100)='',
@Expense_Purpose AS VARCHAR(100)='',
@Activity_Type AS VARCHAR(100)='',
@Activity_State AS VARCHAR(100)='',
@Spend_Type AS VARCHAR(100)='',
@Attendee_FName AS VARCHAR(100)='',
@Attendee_MName AS VARCHAR(100)='',
@Attendee_LName AS VARCHAR(100)='',
@Customer_ID AS VARCHAR(50)='',
@AddressLine1 AS VARCHAR(200)='',
@LocationID AS VARCHAR(50)='',
@External_ExpenseID AS VARCHAR(50)='',
@Expense_Date AS DATETIME,
@Total_Attendees AS INT,
@Total_HCP_Attendees AS INT,
@Total_Amount AS DECIMAL(18,2)=0,
@Employee_Contact AS VARCHAR(100),
@Attendee_FullName AS VARCHAR(100)
AS
--BEGIN
  BEGIN TRAN
 DECLARE
  @intErrorCode INT,@Attendee_ID INT,@Event_ID INT,@StateId INT,@Event_TypeId INT,
  @EventTypeId INT,@COUNT INT,@NatureId INT,@PurposeId INT,@SpendItemId INT,
  @UPEvent_ID INT,@UPAttendee_ID INT,@Individualamount DECIMAL,
  @Event_Name_History  varchar(60),@Event_Type_Id_History INT,
  @Event_State_Id_History INT,@Event_Date_History VARCHAR(19),
  @Submitted_By_History  INT,@Employee_Contact_History VARCHAR(100),
  @Total_Attendees_History INT,@Total_HCP_Attendees_History INT,
  @Total_Amount_History INT,@User_Id INT,@History_id INT
 
 IF(@Activity_Type = 'EVENTEXP')
  SET @Activity_Type = 'Field Force T&E'

 SELECT @EventTypeId = Eventtype_Id FROM PMP_EventType WHERE UPPER([Type_Name]) = UPPER(@Activity_Type)
 SELECT @StateId = State_Id FROM PMP_StateMaster WHERE UPPER(State_initials) = UPPER(@Activity_State)
 IF @EventTypeId = ''
  SET @EventTypeId = NULL
 IF @StateId = ''
  SET @StateId = NULL
     SET @External_ExpenseID = 'MI-' + @External_ExpenseID
  SELECT @NatureId =  PaymentNature_Id FROM PMP_PaymentNature WHERE UPPER(Payment_Nature)=UPPER(@Expense_Nature)
  SELECT @PurposeId = PaymentPurpose_Id FROM PMP_PaymentPurpose WHERE UPPER(PaymentPurpose_Name)=UPPER(@Expense_Purpose)
  SELECT @SpendItemId = SpendItem_Id FROM  PMP_SpendItemMaster WHERE UPPER(SpendItem_Name) =UPPER(@Spend_Type)
  SELECT @User_Id = [User_Id] FROM PMP_UserInfo WHERE Username='MobileIntelligence' 
    
     -------Check Condition for Insert or Update record in Core Tables ----------------
    
     IF (EXISTS(SELECT EI.*, AI.Attendee_LName, AI.Attendee_FName, AI.Attendee_Id
     FROM PMP_Expenses AS EXPI INNER JOIN
     PMP_AttendeeInformation AS AI ON EXPI.Attendeeid = AI.Attendee_Id INNER JOIN
     PMP_EventInformation AS EI ON EXPI.Eventid = EI.Event_Id
     WHERE EI.Event_Type_Id=@EventTypeId
     AND EI.External_Activity_ID=@ExpenseId
     AND EI.Total_Attendees= @Total_Attendees
     AND EI.Total_HCP_Attendees=@Total_HCP_Attendees
     AND EI.Total_Amount=@Total_Amount
     AND EI.Event_Date = @Expense_Date
     AND EI.EmployeeContact=@Employee_Contact
     AND AI.Attendee_FName = @Attendee_FName
     AND AI.Attendee_LName = @Attendee_LName
     AND EXPI.SpendItemid=@SpendItemId
     AND EI.Event_Name=@External_ExpenseID
     AND CustomerID=@Customer_ID
     AND EI.Event_State_Id=@StateId ))
     BEGIN
     -------No Changes in Existing records of Core Tables
      SET @COUNT = 1
     END
 
     ELSE IF(NOT EXISTS(SELECT EI.*, AI.Attendee_LName, AI.Attendee_FName, AI.Attendee_Id
     FROM PMP_Expenses AS EXPI INNER JOIN
     PMP_AttendeeInformation AS AI ON EXPI.Attendeeid = AI.Attendee_Id INNER JOIN
     PMP_EventInformation AS EI ON EXPI.Eventid = EI.Event_Id
     WHERE EI.External_Activity_ID=@ExpenseId 
     --AND EI.Event_Type_Id=@EventTypeId
     AND AI.Attendee_FName = @Attendee_FName
     AND AI.Attendee_LName = @Attendee_LName
     AND EI.Event_Name=@External_ExpenseID))
      BEGIN 
        IF (@Customer_ID <> '')
   BEGIN
          INSERT INTO PMP_EventInformation
   (
     Event_Date,
     Individual_Amount,
     Total_Amount,
     Total_Attendees,
     Total_HCP_Attendees,
     Event_Type_Id,
     EmployeeContact,
     Event_State_Id,
     Request_Date,
     External_Activity_ID,
     Submitted_By,
     [Status],
     Event_Name
         )
     SELECT
     @Expense_Date,
     Individual_Amount,
     @Total_Amount,
     @Total_Attendees,
     @Total_HCP_Attendees,
     @EventTypeId,
     @Employee_Contact,
     @StateId,
     GETDATE(),
     @ExpenseId,
     @User_Id,
     'NEW',
     @External_ExpenseID
     FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
         END
        
      ELSE
        BEGIN
   INSERT INTO PMP_EventInformation_Error
    (
     Event_Date,
     Individual_Amount,
     Total_Amount,
     Total_Attendees,
     Total_HCP_Attendees,
     Event_Type_Id,
     EmployeeContact,
     Event_State_Id,
     Request_Date,
     External_Activity_ID,
     Submitted_By,
     [Status],
     Event_Name
          )
     SELECT
     @Expense_Date,
     Individual_Amount,
     @Total_Amount,
     @Total_Attendees,
     @Total_HCP_Attendees,
     @EventTypeId,
     @Employee_Contact,
     @StateId,
     GETDATE(),
     @ExpenseId,
     @User_Id,
     'NEW',
     @External_ExpenseID
     FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
        END  
     SELECT @Event_ID = @@IDENTITY
    -------------------------Insert Into AttendeeInformation---------
   IF (@Customer_ID <> '')
   BEGIN
    INSERT INTO PMP_AttendeeInformation
    (
       Attendee_FName,
       Attendee_MName,
       Attendee_LName,
       CustomerID
    )
    VALUES
    (
    @Attendee_FName,
    @Attendee_MName,
    @Attendee_LName,
    @Customer_ID
    )
  
   END
   ELSE
   BEGIN
    INSERT INTO PMP_AttendeeInformation_Error
    (
     Attendee_FName,
     Attendee_MName,
     Attendee_LName,
     CustomerID
    )
    VALUES
    (
    @Attendee_FName,
    @Attendee_MName,
    @Attendee_LName,
    @Customer_ID
    )
   END
  SELECT @Attendee_ID = @@IDENTITY
 
   -------------------------Insert Into PMP_Address-------------------------
 
  IF (@Customer_ID <> '' )
  BEGIN 
   INSERT INTO PMP_Address
    (
     Attendee_Id,
     AddressLine1,
     State_Id,
     Location_id
    )
    VALUES
    (
    @Attendee_ID,
    @AddressLine1,
    @StateId,
    @LocationID
    )
   END
    ELSE
     BEGIN 
     INSERT INTO PMP_Address_Error
    (
      Attendee_Id,
      AddressLine1,
      State_Id,
      Location_id
    )
    VALUES
    (
    @Attendee_ID,
    @AddressLine1,
    @StateId,
    @LocationID
    )
      END
    ------------------------------Insert Into PMP_EventAttendeeMapping--------
  
   IF (@Customer_ID <> '')
    BEGIN
    INSERT INTO PMP_EventAttendeeMapping
    (
     Event_Id,
     Attendee_Id
    )
    VALUES
    (
      @Event_ID,
      @Attendee_ID
    )
     END
     ELSE
      BEGIN
    INSERT INTO PMP_EventAttendeeMapping_Error
    (
     Event_Id,
     Attendee_Id
    )
    VALUES
    (
      @Event_ID,
      @Attendee_ID
    )
      END
 
  ---------------------Insert Into PaymentDetailsMapping------------
 
  IF @NatureId = ''
    SET @NatureId = NULL
     IF @PurposeId = ''
    SET @PurposeId = NULL
  IF @SpendItemId = ''
    SET @SpendItemId = NULL
 
  IF (@Customer_ID <> '')
   BEGIN
   INSERT INTO PMP_PaymentDetailsMapping
    (
     PaymentNature_Id,
     PaymentPurpose_Id,
     EventID,
     AttendeeId,
     SpenditemId,
     A_RoleID
    )
   VALUES
    (
     @NatureId,
     @PurposeId,
     @Event_ID,
     @Attendee_ID,
     @SpendItemId,
     5
    )
   END
   ELSE
    BEGIN
    INSERT INTO PMP_PaymentDetailsMapping_Error
    (
     PaymentNature_Id,
     PaymentPurpose_Id,
     EventID,
     AttendeeId,
     SpenditemId,
     A_RoleID
    )
   VALUES
    (
     @NatureId,
     @PurposeId,
     @Event_ID,
     @Attendee_ID,
     @SpendItemId,
     5
    )
    END
   -------------------------Insert Into PMP_Expenses---------
 
  IF (@Customer_ID <> '')
   BEGIN
   INSERT INTO PMP_Expenses
   (
     SpendItemid,
     Eventid,
     Attendeeid,
     Amount
   )
  
   SELECT
   @SpendItemId,
   @Event_ID,
   @Attendee_ID,
   Individual_Amount
   FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
  END
  ELSE
   BEGIN
     INSERT INTO PMP_Expenses_Error
   (
     SpendItemid,
     Eventid,
     Attendeeid,
     Amount
   )
  
    SELECT @SpendItemId,
    @Event_ID,
    @Attendee_ID,
    Individual_Amount FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
    END
        END
       
        ELSE
         BEGIN
         ------------------------------ Update Record ---------------------------------------
          ----------Find EventId and AttendeeId For Update Record----------
           SELECT @UPEvent_ID = EI.Event_Id,@UPAttendee_ID= AI.Attendee_Id FROM PMP_Expenses AS EXPI
     INNER JOIN PMP_AttendeeInformation AS AI ON EXPI.Attendeeid = AI.Attendee_Id
           INNER JOIN PMP_EventInformation AS EI ON EXPI.Eventid = EI.Event_Id
           WHERE EI.External_Activity_ID=@ExpenseId  
           --AND EI.Event_Type_Id=@EventTypeId
           AND AI.Attendee_FName = @Attendee_FName
           AND AI.Attendee_LName = @Attendee_LName
           AND EI.Event_Name=@External_ExpenseID
          ------------------------------------------------------------------
          ----Find Individual_Amount --------
         
           SELECT @Individualamount = Individual_Amount
           FROM dbo.MobileIntelligenceLoadMIL(@ExpenseId,@Attendee_FullName)
          ------------------------------------
          
           --------------------Insert Into History Table---------------------------
         
            SELECT @History_ID = (CASE WHEN MAX(History_id) IS NULL THEN 1 ELSE MAX(History_id) + 1 END)    
      FROM PMP_Activity_History
  
   SELECT @Event_Name_History = Event_Name,
            @Event_Type_Id_History=Event_Type_Id,
            @Event_State_Id_History = Event_State_Id,
            @Event_Date_History = CONVERT(VARCHAR(19),Event_Date,120),
            @Employee_Contact_History = EmployeeContact,
      @Total_Attendees_History = Total_Attendees,
      @Total_HCP_Attendees_History = Total_HCP_Attendees,
      @Total_Amount_History = Total_Amount
      FROM PMP_EventInformation WHERE Event_Id = @UPEvent_ID
           
           IF(@Total_Amount_History <> @Total_Amount)
       BEGIN
       INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Total Amount',@Total_Amount_History,@Total_Amount)
       END
 
     IF(@Event_Type_Id_History <> @EventTypeId )
    BEGIN
       INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Event Type Id',@Event_Type_Id_History,@Activity_Type)
       END
      
         IF(@Event_State_Id_History <> @StateId)
   BEGIN
      INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Event State Id',@Event_State_Id_History,@StateId)
   END
      IF(@Total_HCP_Attendees_History <> @Total_HCP_Attendees)
      BEGIN
         INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Total HCP Attendees',@Total_HCP_Attendees_History,@Total_HCP_Attendees)
      END
 
            IF(@Total_Attendees_History <> @Total_Attendees)
      BEGIN
     INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Total Attendees',@Total_Attendees_History,@Total_Attendees)
      END
     
      IF(@Event_Date_History <> CONVERT(VARCHAR(19),@Expense_Date,120))
   BEGIN
        INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Event Date',@Event_Date_History,@Expense_Date)
      END
     
      IF(@Employee_Contact_History <> @Employee_Contact)
      BEGIN
       INSERT INTO PMP_Activity_History VALUES(@UPEvent_ID,@History_ID,GETDATE() ,@User_Id,'MobileIntelligence',5,25,@Activity_Type,'','','','','','Employee Contact',@Employee_Contact_History,@Employee_Contact)
      END 
         
          ---------- Update PMP_EventInformation Table ------------
         
          UPDATE PMP_EventInformation SET  Event_Date = @Expense_Date, 
    Individual_Amount =  @Individualamount,Total_Amount = @Total_Amount,
    Total_Attendees = @Total_Attendees,Total_HCP_Attendees = @Total_HCP_Attendees,
    Event_Type_Id = @EventTypeId,EmployeeContact = @Employee_Contact,
    Event_State_Id =@StateId,Request_Date =GETDATE(),External_Activity_ID=@ExpenseId,  
    Submitted_By=@User_Id,[Status] = 'NEW',Event_Name = @External_ExpenseID  
    WHERE Event_Id = @UPEvent_ID  
   
       
        
  
  ---------------------------------------------------
         
          ---------- Update PMP_AttendeeInformation Table ------------
     UPDATE PMP_AttendeeInformation SET Attendee_FName = @Attendee_FName,
     Attendee_MName = @Attendee_MName,Attendee_LName = @Attendee_LName, 
     CustomerID = @Customer_ID  
     WHERE Attendee_Id = @UPAttendee_ID  
    
   ---------- Update PMP_Address Table ------------
  
    UPDATE  PMP_Address SET AddressLine1 = @AddressLine1,
    State_Id =  @StateId,Location_id = @LocationID  
    WHERE Attendee_Id = @UPAttendee_ID   
  
   ------------------------------------------------
         ---------- Update PMP_EventAttendeeMapping Table -
         
           UPDATE PMP_EventAttendeeMapping SET Event_Id = @UPEvent_ID,
   Attendee_Id =@UPAttendee_ID
   WHERE Attendee_Id = @UPAttendee_ID
  ------------------------------------------------
 
  ---------- Update PMP_PaymentDetailsMapping ----
            UPDATE PMP_PaymentDetailsMapping SET PaymentNature_Id = @NatureId,
   PaymentPurpose_Id = @PurposeId,EventID = @UPEvent_ID,
   AttendeeId = @UPAttendee_ID,SpenditemId = @SpendItemId,A_RoleID = 5
      WHERE AttendeeId = @UPAttendee_ID
  -----------------------------------------------
  ---------- Update PMP_Expenses ----
   UPDATE PMP_Expenses SET  SpendItemid = @SpendItemId,
   Eventid = @UPEvent_ID,Attendeeid = @UPAttendee_ID,
   Amount = @Individualamount   
   WHERE AttendeeId = @UPAttendee_ID  
    
  
         END
       
    SELECT @intErrorCode = @@ERROR
 IF (@intErrorCode <> 0) GOTO PROBLEM
 COMMIT TRAN
 ----------------------------------------
  PROBLEM:
 IF (@intErrorCode <> 0)
 BEGIN
     ---PRINT 'Unexpected error occurred!'
   ROLLBACK TRAN
    END

create table value Function in SQL Server

Create FUNCTION [dbo].[MobileIntelligenceLoadMIL]
(
  @ExpenseId AS VARCHAR(50)='',
  @Attendee_FullName AS VARCHAR(100)=''
)
RETURNS @retMobileIntelligenceInformation TABLE
(
 Individual_Amount DECIMAL(18,2)
)
AS
BEGIN
    DECLARE
 @Individual_Amount Decimal
 --SELECT @Individual_Amount = (Expense_Amount/Total_Attendees) FROM  PMP_ExpenseStaging
 --WHERE Expense_Id = @ExpenseId AND Full_Name = @Attendee_FullName AND Convert(VARCHAR(10), CMDate,121) = Convert(VARCHAR(10),GetDate(),121)

 SELECT @Individual_Amount = (Expense_Amount/Total_Attendees) FROM PMP_ExpenseStaging
 WHERE Expense_Id = @ExpenseId
 AND Full_Name = @Attendee_FullName
 AND Convert(VARCHAR(10), CMDate,111) = Convert(VARCHAR(10),GetDate(),111)

 INSERT @retMobileIntelligenceInformation
 SELECT @Individual_Amount AS Individual_Amount
 FROM PMP_ExpenseStaging
 WHERE Expense_Id = @ExpenseId
 AND LTRIM(RTRIM(Full_Name)) = LTRIM(RTRIM(@Attendee_FullName))
 AND Convert(VARCHAR(10), CMDate,111) = Convert(VARCHAR(10),GetDate(),111)
 RETURN;
END

Sql important querys to search records

+++++++++++++Display SP list according modified date descending++++++++++++++++
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
order by modify_date desc
+++++++++ Display Table list according modified date ++++++++++++++++++++
SELECT name, modify_date
FROM sys.objects
WHERE type = 'U'
order by modify_date desc
-----------------------------------
select obj.Name, * from syscomments  cm
Left outer join  sys.objects  obj on cm.id=obj.object_id
where cm.text like '%netra%'
-------------------search column names------------
select table_name from information_schema.columns where column_name='State_Id'

Encryption........Decryption functionality on Asp.net

Add one class file ....name as Encryption.cs

using System;
using System.Configuration;
using System.Web;
using System.Web.Security;
using Paragon.Security.Cryptography;
using Microsoft.Win32;

/// <summary>
/// Summary description for Encryption
/// </summary>
public class Encryption
{
    protected string encryptionKey = "";
    public Encryption()
    {
        try
        {
            encryptionKey = System.Configuration.ConfigurationManager.AppSettings["EncryptionKey"];
            if (encryptionKey == null || encryptionKey == "")
                throw new Exception("Encryption key is missing or is blank");

        }
        catch (Exception ex)
        {
            //GMSLogFile.LogBLError(ex.Message);
            //throw ex;
        }
    }
    public string EncryptString(string dataToEncrypt)
    {
        string encryptedString = "";
        try
        {
            if (dataToEncrypt != "")
            {
                encryptedString = EncryptionHelper.EncryptData(EncryptionHelper.EncryptionMethod.Rijndael, EncryptionHelper.HashMethod.MD5, dataToEncrypt, encryptionKey);
            }
           
        }
        catch (Exception ex)
        {
        }
        return encryptedString;
    }
    public string DecryptString(string dataToDecrypt)
    {
        string decryptedString = "";
        try
        {
            if (dataToDecrypt != "")
            {
                decryptedString = EncryptionHelper.DecryptData(EncryptionHelper.EncryptionMethod.Rijndael, EncryptionHelper.HashMethod.MD5, dataToDecrypt, encryptionKey);
            }
         
        }
        catch (Exception ex)
        {

        }
        return decryptedString;
    }
}

add  dll file name as Paragon.Security.Cryptography.dll

add key to web.config file under <appsetting> section
<add key="EncryptionKey" value="AxrLnHejuKEmpKvFqLpbQedndvpgTsDF" />
create object of Encryption class globally
  Encryption encry;
1)for encrypt decrypted code we can use
  tempPass = FirstName + usid.ToString() + lastname;
   encry = new Encryption();
  string pass = encry.EncryptString(tempPass);
in this sentence encrypt the value of variable tempPass and stored it to pass variable
2)
for decrypt  encrypted code we can use
create object of Encryption class globally
  Encryption encry;
 encry = new Encryption();
string strpass = Convert.ToString(dr["Password"]);
string decpass = encry.DecryptString(strpass);
in this sentence decrypt the value of variable strpass and stored it to decpass variable

PMP_BGCMAffiliation

Create PROC [dbo].[InsertInto_BGCMAffiliation]
     @From_BGCM_ID as VARCHAR(100),
           @To_BGCM_ID as VARCHAR(100),
           @Last_Modified_Date as datetime,
           @Create_Date as datetime
          
AS BEGIN
Declare @FBGCM_ID as VARCHAR(100),@TBGCM_ID as VARCHAR(100)

SELECT @FBGCM_ID = BGCM_ID FROM PMP_BGCMCustomer
WHERE BCE_ID = @From_BGCM_ID AND Customer_Type = 'Physician'

SELECT  @TBGCM_ID = BGCM_ID FROM PMP_BGCMCustomer WHERE BCE_ID  = @To_BGCM_ID AND Customer_Type <> 'Physician'
INSERT INTO PMP_BGCMAffiliation
(From_BGCM_ID,To_BGCM_ID,Last_Modified_Date,Last_Modified_Source,Create_Date,Affiliation_Status_Code)
    VALUES
(@FBGCM_ID,@TBGCM_ID,@Last_Modified_Date,'CE',@Create_Date,'Active')
END

CURSOR EXAMPLE SQL SERVER

Create PROC [dbo].[InsertInto_ContactBGCMAddress]     
   @BCE_Address_ID varchar(100)     
     ,@Address_Line_1 varchar(100)     
     ,@City varchar(50)     
     ,@State_Province varchar(100)     
     ,@Postal_Area varchar(100)     
     ,@Country varchar(100)     
     ,@Phone_Number varchar(100)  
AS
BEGIN   
 Begin tran insertContact
  DECLARE @BGCM_ID INT, @BiometContactId VARCHAR(100)  
  Declare curContact Cursor
  For 
  select From_BGCM_ID from PMP_BGCMAffiliation where To_BGCM_ID =  @BCE_Address_ID               
  open  curContact
  Fetch next from curContact into @BiometContactId
  While @@FETCH_STATUS = 0
  Begin
 
   SELECT @BGCM_ID = BGCM_ID FROM PMP_BGCMCustomer WHERE BCE_ID= @BiometContactId  and Customer_Type = 'Physician'
   INSERT INTO PMP_BGCMAddress     
     ([BGCM_ID]     
     ,[BCE_Address_ID]     
     ,[Last_Modified_Date]     
     ,[Last_Modified_Source]     
     ,[Address_Line_1]     
     ,[City]     
     ,[State_Province]     
     ,[Postal_Area]     
     ,[Country]     
     ,[Phone_Number]     
     ,[Create_Date]     
     ,[Address_Status_Code])     
   VALUES     
     (@BGCM_ID     
     ,@BCE_Address_ID     
     ,getdate()     
     ,'CE'     
     ,@Address_Line_1     
     ,@City     
     ,@State_Province     
     ,@Postal_Area     
     ,@Country     
     ,@Phone_Number     
     ,getdate()     
     ,'Active')
    

    --Update PMP_BGCMAffiliation SET From_BGCM_ID = @BGCM_ID where From_BGCM_ID = @BiometContactId
      
   Fetch next from curContact into @BiometContactId
  End
  close curContact
  DEALLOCATE curContact
 Commit tran insertContact
End

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