WARNING: This is not kids stuff. Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.

Note: This feature requires Control 5.75 or higher.

The stored procedure can be used to add a new contact. You only need to (and are only allowed to) specify one of the following:

@AccountID - The ID of the company in the Account Table

@CompanyName - The exact text name of the company.

You must also provide

@FirstName or @ LastName - One of these fields must be provided to insert a contact.

Not all of the information you can add in Control is included in this standard stored procedure, though someone familiar with SQL could extend this accordingly. All of the records inserted are wrapped in a single transaction. This ensures that the records are either all inserted, or none are inserted.

The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.

This inserts a basic contact.

EXEC csp_ImportContact
        --
        -- All calls must supply *one* and only one of the following values
        --
        @AccountID = 10445,
        @CompanyName = NULL, -- The company name.  Must match EXACTLY and be the only one
 
         -- Contact Name
        @LastName = 'James',
        @FirstName = 'Sara',
 
        -- Contact Additional Phone #1
        @AddCellPhone = 1, -- Set to 1 to Add a Cell Phone to the contact
        @CellPhoneAC = '318',
        @CellPhoneNumber = '9019392',

This inserts a sample contact with

• a different office phone number,
• a different shipping address

EXEC csp_ImportContact
        --
        -- All calls must supply *one* and only one of the following values
        --
        @AccountID = 10445,
        @CompanyName = NULL, -- The company name.  Must match EXACTLY and be the only one
 
         -- Contact Name
        @FirstName = 'Sara',
        @POSITION  = NULL,
        @EmailAddress  = NULL,
        @IsPrimaryContact = 1,
        @IsBillingContact = 1,
 
 
        -- Office Phone
        @UseDefaultPhone = 1,
        @WorkPhoneAC = NULL,
        @WorkPhoneNumber = NULL,
 
        -- Contact Additional Phone #1
        @AddCellPhone = 1, -- Set to 1 to Add a Cell Phone to the contact
        @CellPhoneAC = '318',
        @CellPhoneNumber = '9019392',
 
        -- Company Shipping Address Info
        @UseCompanyShippingAddress = 1,  -- Set to 1 to use the same shipping as the company
        @SStreetAddress1 = NULL,
        @SStreetAddress2 = NULL,
        @SCity           = NULL,
        @SState          = NULL,
        @SPostalCode     = NULL,
 
        @Notes = ''

-- =============================================
-- Author:        Cyrious Sofware
-- Create date: May-2016
-- Description:    This stored procedure imports a contact record into Control.
--                Many of the parameters are option, but if not supplied will used
--                The default behavior.
--              
-- Returns:     New ID for Contact (AccountContact.ID)
-- =============================================
ALTER PROCEDURE csp_ImportContact
    @AccountID            INT,
    @Title              VARCHAR(25) = NULL,
    @FirstName          VARCHAR(25) = NULL,
    @LastName           VARCHAR(25) = NULL,
    @POSITION           VARCHAR(50) = NULL,
    @EmailAddress       VARCHAR(50) = NULL,
    @IsPrimaryContact   BIT = 0,
    @IsBillingContact   BIT = 0,
 
 
    -- Office Phone #1
    @UseDefaultPhone  BIT = 1,
    @WorkPhoneAC      VARCHAR(10) = NULL,
    @WorkPhoneNumber  VARCHAR(25) = NULL,
 
    -- Company Billing Address Info
    @UseCompanyBillingAddress bit = 1,  -- Set to 1 to use the same shipping as the company
    @BillingAddress1 VARCHAR(256) = NULL,
    @BillingAddress2 VARCHAR(256) = NULL,
    @BillingCity           VARCHAR(256) = NULL,
    @BillingState          VARCHAR(256) = NULL,
    @BillingPostalCode     VARCHAR(256) = NULL,
 
    -- Company Shipping Address Info
    @UseCompanyShippingAddress bit = 1,  -- Set to 1 to use the same shipping as the company
    @ShippingAddress1 VARCHAR(256) = NULL,
    @ShippingAddress2 VARCHAR(256) = NULL,
    @ShippingCity           VARCHAR(256) = NULL,
    @ShippingState          VARCHAR(256) = NULL,
    @ShippingPostalCode     VARCHAR(256) = NULL,
 
    @Notes        VARCHAR(4096) = '',
 
    -- Contact Additional Phone #1
    @AddCellPhone     BIT = 0,    -- Set to 1 to Add a Cell Phone to the contact
    @CellPhoneAC      VARCHAR(10) = NULL,
    @CellPhoneNumber  VARCHAR(25) = NULL,
 
    -- Some OUTPUT Parameters in case the caller wants any of these value back
    @ContactID          INT     = NULL  OUTPUT,
    @BillingAddressID         INT     = NULL  OUTPUT,
    @ShippingAddressID         INT     = NULL  OUTPUT,
    @JournalID          INT     = NULL  OUTPUT
 
AS
BEGIN
    SET NOCOUNT ON;
 
    --
    -- Step 0. Validate the Input
    --
 
    DECLARE @Logs                               TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT,
                                                       Summary VARCHAR(255),
                                                       Detail VARCHAR(2000)
                                                       );
    DECLARE @ValidationError VARCHAR(MAX) = '';
 
    IF (@AccountID IS NULL) SET @ValidationError = @ValidationError + 'AccountID required.  ';
    IF (COALESCE(@FirstName, '') = '') AND (COALESCE(@LastName, '') = '') SET @ValidationError = @ValidationError + 'Either a First Name or a Last name is required.  ';
 
    IF (@ValidationError <> '')
    BEGIN
        INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
        VALUES(
            NULL, 3000, @AccountID, 1,
            'Contact '+@FirstName+' '+@LastName+'for Account.ID '+CONVERT(VARCHAR(12), @AccountID)+' Import FAILED due to Validation Errors.',
            'Validation Errors: '+@ValidationError
        )
 
        SELECT * FROM @Logs;
        RETURN;
    END;
 
 
    -- Company info Pulled from Store or looked up
    DECLARE @PhoneCountryCode   VARCHAR(10);
    DECLARE @DivisionID         INT;
    DECLARE @SalespersonID      INT;
 
    -- Retrieve Defaults Information from  databases
    SELECT  TOP 1
            @PhoneCountryCode = COALESCE(DefaultCountryCode, '1')
    FROM Store
    WHERE ID > 0
    ORDER BY ID;
 
    -- Declare ID fields used in the process;
    DECLARE @WorkPhoneType      INT = 10;  -- Business.  From Element Table, where ClassTypeID = 4101
    DECLARE @CellPhoneType      INT = 12;  -- Mobile.  From Element Table, where ClassTypeID = 4101
 
    -- Load some default information
    SELECT @DivisionID = DivisionID,
           @SalespersonID = SalespersonID1,
           @ShippingAddressID = ShippingAddressID,
           @BillingAddressID = BillingAddressID
    FROM Account
    WHERE ID = @AccountID;
 
    -- Define some error variables in case we need them
    DECLARE @DT              SMALLDATETIME = GetDate();
    DECLARE @ComputerName    VARCHAR(25) = @@ServerName;
    DECLARE @NewLine         CHAR(2) =  CHAR(10)+CHAR(13);
 
 
-- Step 1, Request New IDs
-- 
    SET @ContactID          = (SELECT dbo.csf_chapi_nextid( 3000, 1));
    SET @JournalID          = (SELECT dbo.csf_chapi_nextid( 20510, 1)); -- Company Activity 
 
    DECLARE @AddressLinkID      INT     = (SELECT dbo.csf_chapi_nextid( 4002, 2)); -- Need 2 Address Links
    DECLARE @PhoneNumberID      INT     = (SELECT dbo.csf_chapi_nextid( 4100, 2)); -- Work Phone for Business (and Cell if needed)
 
    IF (@UseCompanyBillingAddress = 0)  SET @BillingAddressID = (SELECT dbo.csf_chapi_nextid( 4001, 1));
    IF (@UseCompanyShippingAddress = 0) SET @ShippingAddressID = (SELECT dbo.csf_chapi_nextid( 4001, 1));
 
-- Step 2, Begin a Transaction so the whole thing succeeds or fails together 
--
 
BEGIN TRANSACTION
 
   BEGIN TRY
 
        --
        -- Step 1, Create the associated CONTACT RECORDS
        --
 
        -- Insert Contact
 
        INSERT INTO [dbo].[AccountContact] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[Notes] ,[FirstName] ,[LastName] ,[Title] ,[POSITION], [EmailAddress] ,[MainPhoneNumberID] ,[MainFaxNumberID] ,[AccountID] ,[IsPrimaryContact] ,[IsAccountingContact] ,[BillingAddressID] ,[ShippingAddressID] ,[BirthDateMonth] ,[BirthDateDay] ,[DefaultPaymentExpDate] ,[DefaultPaymentTrackingNumber] ,[DefaultPaymentNameOnCard] ,[DefaultPaymentTypeID] ,[CCBillingAddress] ,[DefaultPaymentVCode] ,[UserID] ,[SalespersonID1] ,[SalespersonID2] ,[SalespersonID3] ,[UseCompanySalespeople] ,[IsCCNumEncrypt] ,[DisplayNumber] ,[BirthDate] ,[IsVCodeEncrypted] ,[PrimaryNumber] ,[PriNumberTypeID] ,[PriNumberTypeText] ,[SecondaryNumber] ,[SecNumberTypeID] ,[SecNumberTypeText] ,[PaymentAddressID] ,[CCSwiped] ,[SendBillingAddress] ,[IDNumber] ,[ImageID] ,[MiddleName] ,[ContactType] ,[GenderType] ,[NumOfMakeups] ,[ThirdNumber] ,[ThirdNumberTypeID] ,[ThirdNumberTypeText] ,[DefaultPaymentBankReference] ,[DefaultPaymentBankCode] ,[DefaultPaymentBranchCode] ,[DefaultPaymentCIN] ,[DefaultPaymentState] ,[DefaultPaymentCCAccount] ,[PaymentAddressLinkID] ,[CCCSCustomerGuid] ,[UseShippingAccountInfo] ,[DefaultShippingAccountNumber] ,[DefaultShippingCarrierID] ,[DefaultShippingCarrierClassTypeID] ,[DefaultShippingAccountPostalCode] ,[ShippingMethodLinksXML] ,[PaymentAddressOV]) 
 
        VALUES
        ( @ContactID  -- <ID, int,>
        , -1     -- ,<StoreID, int,>
        , 3000     -- ,<ClassTypeID, int,>
        , 'SQLBridge'     -- ,<ModifiedByUser, nvarchar(25),>
        , @ComputerName     -- ,<ModifiedByComputer, nvarchar(25),>
        , @DT          -- ,<ModifiedDate, datetime,>
        , 0    -- ,<SeqID, int,>
        , 0    -- ,<IsSystem, bit,>
        , 1    -- ,<IsActive, bit,>
        , NULL  -- <Notes, nvarchar(max),>
        , @FirstName  -- <FirstName, nvarchar(25),>
        , @LastName  -- <LastName, nvarchar(25),>
        , @Title  -- <Title, nvarchar(10),>
        , @POSITION  -- <Position, nvarchar(25),>
        , @EmailAddress  -- <EmailAddress, nvarchar(50),>
        , @PhoneNumberID+0  -- <MainPhoneNumberID, int,>
        , CASE WHEN (@AddCellPhone = 1) THEN @PhoneNumberID+2 ELSE NULL END  -- <MainFaxNumberID, int,>
        , @AccountID  -- <AccountID, int,>
        , @IsPrimaryContact  -- <IsPrimaryContact, bit,>
        , @IsBillingContact  -- <IsAccountingContact, bit,>
        , @BillingAddressID  -- <BillingAddressID, int,>
        , @ShippingAddressID  -- <ShippingAddressID, int,>
        , NULL  -- <BirthDateMonth, int,>
        , NULL  -- <BirthDateDay, int,>
        , NULL  -- <DefaultPaymentExpDate, datetime,>
        , NULL  -- <DefaultPaymentTrackingNumber, varchar(25),>
        , NULL  -- <DefaultPaymentNameOnCard, varchar(25),>
        , NULL  -- <DefaultPaymentTypeID, int,>
        , NULL  -- <CCBillingAddress, varchar(25),>
        , NULL  -- <DefaultPaymentVCode, varchar(50),>
        , NULL  -- <UserID, int,>
        , @SalespersonID  -- <SalespersonID1, int,>
        , NULL  -- <SalespersonID2, int,>
        , NULL  -- <SalespersonID3, int,>
        , 1  -- <UseCompanySalespeople, bit,>
        , 1  -- <IsCCNumEncrypt, bit,>
        , ''  -- <DisplayNumber, nvarchar(50),>
        , NULL  -- <BirthDate, datetime,>
        , 1  -- <IsVCodeEncrypted, bit,>
        , '('+@WorkPhoneAC+') '+@WorkPhoneNumber  -- <PrimaryNumber, varchar(75),>
        , @WorkPhoneType  -- <PriNumberTypeID, int,>
        , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- <PriNumberTypeText, varchar(50),>
        , CASE WHEN (@AddCellPhone = 1) THEN '('+@CellPhoneAC+') '+@CellPhoneNumber ELSE '' END  -- <SecondaryNumber, varchar(75),>
        , @CellPhoneType  -- <SecNumberTypeID, int,>
        , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- <SecNumberTypeText, varchar(50),>
        , NULL  -- <PaymentAddressID, int,>
        , 0  -- <CCSwiped, bit,>
        , 1  -- <SendBillingAddress, bit,>
        , NULL  -- <IDNumber, nvarchar(50),>
        , NULL  -- <ImageID, int,>
        , ''  -- <MiddleName, nvarchar(50),>
        , 0  -- <ContactType, int,>
        , 0  -- <GenderType, int,>
        , NULL  -- <NumOfMakeups, int,>
        , NULL  -- <ThirdNumber, nvarchar(75),>
        , NULL  -- <ThirdNumberTypeID, int,>
        , NULL  -- <ThirdNumberTypeText, varchar(50),>
        , NULL  -- <DefaultPaymentBankReference, varchar(50),>
        , NULL  -- <DefaultPaymentBankCode, varchar(50),>
        , NULL  -- <DefaultPaymentBranchCode, varchar(50),>
        , NULL  -- <DefaultPaymentCIN, varchar(50),>
        , NULL  -- <DefaultPaymentState, varchar(50),>
        , NULL  -- <DefaultPaymentCCAccount, varchar(50),>
        , NULL  -- <PaymentAddressLinkID, int,>
        , NULL  -- <CCCSCustomerGuid, varchar(50),>
        , 0  -- <UseShippingAccountInfo, bit,>
        , NULL  -- <DefaultShippingAccountNumber, varbinary(100),>
        , -1  -- <DefaultShippingCarrierID, int,>
        , -1  -- <DefaultShippingCarrierClassTypeID, int,>
        , NULL  -- <DefaultShippingAccountPostalCode, nvarchar(25),>
        , NULL  -- <ShippingMethodLinksXML, nvarchar(max),>
        , 0  -- <PaymentAddressOV, bit,>
        );
 
 
        -- Insert ContactUDF (Nothing by default)
 
        INSERT INTO [dbo].[AccountContactUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ) 
 
        VALUES
        ( @ContactID  -- <ID, int,>
        , -1     -- ,<StoreID, int,>
        , 3001     -- ,<ClassTypeID, int,>
        , 'SQLBridge'     -- ,<ModifiedByUser, nvarchar(25),>
        , @ComputerName     -- ,<ModifiedByComputer, nvarchar(25),>
        , @DT  -- ,<ModifiedDate, datetime,>
        , 0    -- ,<SeqID, int,>
        , 0    -- ,<IsSystem, bit,>
        , 1    -- ,<IsActive, bit,>
        );
 
 
        -- Insert Billing Address if different.
 
        IF (@UseCompanyBillingAddress = 0)
        BEGIN
            INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError])
 
            VALUES
            ( @BillingAddressID   -- <ID, int,>
            , -1           -- ,<StoreID, int,>
            , 4001         -- ,<ClassTypeID, int,>
            , 'SQLBridge'  -- ,<ModifiedByUser, nvarchar(25),>
            , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
            , @DT    -- ,<ModifiedDate, datetime,>
            , 0            -- ,<SeqID, int,>
            , 0            -- ,<IsSystem, bit,>
            , 1            -- ,<IsActive, bit,>
            , @BillingAddress1   -- <StreetAddress1, varchar(40),>
            , @BillingAddress2   -- <StreetAddress2, varchar(40),>
            , @BillingCity   -- <City, varchar(25),>
            , @BillingState   -- <State, varchar(50),>
            , ''   -- <County, varchar(50),>
            , @ShippingPostalCode   -- <PostalCode, varchar(50),>
            , 'US'   -- <Country, varchar(50),>
            , @BillingAddress1  + @NewLine
            + (CASE WHEN len(COALESCE(@BillingAddress2, '')) > 1 THEN @BillingAddress2 + @NewLine ELSE '' END) 
            + @BillingCity + ', ' + @BillingState + '  ' + @BillingPostalCode -- <FormattedText, varchar(max),>
            , NULL   -- <TaxClassID, int,>
            , 0   -- <IsValidated, bit,>
            , NULL   -- <ValidatedAddress, varchar(max),>
            , 0   -- <HasValidationError, bit,>
            , NULL   -- <ValidationError, varchar(max),>
            );
        END;
 
        -- Insert Shipping Address if different.
 
        IF (@UseCompanyShippingAddress = 0)
        BEGIN
            INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError])
 
            VALUES
            ( @ShippingAddressID   -- <ID, int,>
            , -1           -- ,<StoreID, int,>
            , 4001         -- ,<ClassTypeID, int,>
            , 'SQLBridge'  -- ,<ModifiedByUser, nvarchar(25),>
            , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
            , @DT    -- ,<ModifiedDate, datetime,>
            , 0            -- ,<SeqID, int,>
            , 0            -- ,<IsSystem, bit,>
            , 1            -- ,<IsActive, bit,>
            , @ShippingAddress1   -- <StreetAddress1, varchar(40),>
            , @ShippingAddress2   -- <StreetAddress2, varchar(40),>
            , @ShippingCity   -- <City, varchar(25),>
            , @ShippingState   -- <State, varchar(50),>
            , ''   -- <County, varchar(50),>
            , @ShippingPostalCode   -- <PostalCode, varchar(50),>
            , 'US'   -- <Country, varchar(50),>
            , @ShippingAddress1  + @NewLine
            + (CASE WHEN len(COALESCE(@ShippingAddress2, '')) > 1 THEN @ShippingAddress2 + @NewLine ELSE '' END) 
            + @ShippingCity + ', ' + @ShippingState + '  ' + @ShippingPostalCode -- <FormattedText, varchar(max),>
            , NULL   -- <TaxClassID, int,>
            , 0   -- <IsValidated, bit,>
            , NULL   -- <ValidatedAddress, varchar(max),>
            , 0   -- <HasValidationError, bit,>
            , NULL   -- <ValidationError, varchar(max),>
            );
        END;
 
        -- Insert Contact Address Links  (Billing & Shipping)
 
        INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName])
 
        VALUES
        ( @AddressLinkID+0   -- <ID, int,>
        , -1           -- ,<StoreID, int,>
        , 4002         -- ,<ClassTypeID, int,>
        , 'SQLBridge'  -- ,<ModifiedByUser, nvarchar(25),>
        , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
        , @DT          -- ,<ModifiedDate, datetime,>
        , 0            -- ,<SeqID, int,>
        , 0            -- ,<IsSystem, bit,>
        , 1            -- ,<IsActive, bit,>
        , (CASE WHEN @UseCompanyBillingAddress=1 THEN 0 ELSE 1 END)   -- <IsMaster, bit,>
        , @ContactID   -- <ParentID, int,>
        , 3000   -- <ParentClassTypeID, smallint,>
        , 10   -- Shipping Address -- <AddressTypeID, tinyint,>
        , @BillingAddressID   -- <AddressID, int,>
        , 'Billing'   -- <AddressName, varchar(50),>
        , 0   -- <IsOneTimeCompany, bit,>
        , NULL   -- <CompanyName, varchar(100),>
        , NULL   -- <ContactName, varchar(100),>
        );
 
 
        INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName])
 
        VALUES
        ( @AddressLinkID+1   -- <ID, int,>
        , -1           -- ,<StoreID, int,>
        , 4002         -- ,<ClassTypeID, int,>
        , 'SQLBridge'  -- ,<ModifiedByUser, nvarchar(25),>
        , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
        , @DT          -- ,<ModifiedDate, datetime,>
        , 0            -- ,<SeqID, int,>
        , 0            -- ,<IsSystem, bit,>
        , 1            -- ,<IsActive, bit,>
        , (CASE WHEN @UseCompanyShippingAddress=1 THEN 0 ELSE 1 END )   -- <IsMaster, bit,>
        , @ContactID   -- <ParentID, int,>
        , 3000   -- <ParentClassTypeID, smallint,>
        , 11   -- Shipping Address -- <AddressTypeID, tinyint,>
        , @ShippingAddressID   -- <AddressID, int,>
        , 'Shipping'   -- <AddressName, varchar(50),>
        , 0   -- <IsOneTimeCompany, bit,>
        , NULL   -- <CompanyName, varchar(100),>
        , NULL   -- <ContactName, varchar(100),>
        );
 
        -- Insert Contact Phone Numbers (Work and Cell)
 
        INSERT INTO [dbo].[PhoneNumber]  ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText])
 
        VALUES
        ( @PhoneNumberID+0 -- <ID, int,>
        , -1           -- ,<StoreID, int,>
        , 4100         -- ,<ClassTypeID, int,>
        , 'SQLBridge'  -- ,<ModifiedByUser, nvarchar(25),>
        , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
        , @DT          -- ,<ModifiedDate, datetime,>
        , 0            -- ,<SeqID, int,>
        , 0            -- ,<IsSystem, bit,>
        , 1            -- ,<IsActive, bit,>
        , @ContactID   -- <ParentID, int,>
        , -1     -- <ParentStoreID, smallint,>
        , 3000   -- <ParentClassTypeID, smallint,>
        , @WorkPhoneType   -- <PhoneNumberTypeID, int,>
        , @PhoneCountryCode  -- <CountryCode, varchar(20),>
        , @WorkPhoneAC   -- <AreaCode, varchar(20),>
        , @WorkPhoneNumber   -- <PhoneNumber, varchar(50),>
        , ''   -- <Extension, varchar(20),>
        , '('+@WorkPhoneAC+') '+@WorkPhoneNumber   -- <FormattedText, varchar(150),>
        , 0   -- <PhoneNumberIndex, int,>
        , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- <PhoneNumberTypeText, varchar(100),>
        );
 
        IF (@AddCellPhone = 1)
        BEGIN
            INSERT INTO [dbo].[PhoneNumber]  ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText])
 
            VALUES
            ( @PhoneNumberID+1 -- <ID, int,>
            , -1           -- ,<StoreID, int,>
            , 4100         -- ,<ClassTypeID, int,>
            , 'SQLBridge'  -- ,<ModifiedByUser, nvarchar(25),>
            , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),>
            , GetDate()    -- ,<ModifiedDate, datetime,>
            , 0            -- ,<SeqID, int,>
            , 0            -- ,<IsSystem, bit,>
            , 1            -- ,<IsActive, bit,>
            , @ContactID   -- <ParentID, int,>
            , -1     -- <ParentStoreID, smallint,>
            , 3000   -- <ParentClassTypeID, smallint,>
            , @CellPhoneType   -- <PhoneNumberTypeID, int,>
            , @PhoneCountryCode  -- <CountryCode, varchar(20),>
            , @CellPhoneAC   -- <AreaCode, varchar(20),>
            , @CellPhoneNumber   -- <PhoneNumber, varchar(50),>
            , ''   -- <Extension, varchar(20),>
            , '('+@CellPhoneAC+') '+@CellPhoneNumber   -- <FormattedText, varchar(150),>
            , 0   -- <PhoneNumberIndex, int,>
            , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- <PhoneNumberTypeText, varchar(100),>
            );
        END; -- of adding cell phone
 
 
        --
        -- Step 4, If they are setting this as the Primary or Billing Contact, we need to adjust the existings ones  
        --
 
        IF (@IsPrimaryContact =1)
        BEGIN
            -- Store the Old Primary
            DECLARE @OldPrimaryContactID INT = (SELECT PrimaryContactID FROM Account WHERE ID = @AccountID);
 
            -- Update the Customer Record
            UPDATE Account
            SET SeqID = SeqID + 1, PrimaryContactID = @ContactID
            WHERE ID = @AccountID;
 
            -- And se thte Old Contact ID so it is not primary
            UPDATE AccountContact
            SET SeqID = SeqID + 1, IsPrimaryContact = 0
            WHERE ID = @OldPrimaryContactID;        
        END;
 
 
        IF (@IsBillingContact =1)
        BEGIN
            -- Store the Old Primary
            DECLARE @OldBillingContactID INT = (SELECT AccountingContactID FROM Account WHERE ID = @AccountID);
 
            -- Update the Customer Record
            UPDATE Account
            SET SeqID = SeqID + 1, AccountingContactID = @ContactID
            WHERE ID = @AccountID;
 
            -- And se thte Old Contact ID so it is not primary
            UPDATE AccountContact
            SET SeqID = SeqID + 1, IsAccountingContact = 0
            WHERE ID = @OldPrimaryContactID;        
        END;
 
        --
        -- Step 5, Create a note in the JOURNAL record
        --
 
        INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime]  ,[CompletedByID] ,[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID]  ,[EndGLGroupID]  ,[AccountID] ,[AccountClassTypeID]  ,[ContactID]  ,[ContactClassTypeID]  ,[TransactionID] ,[TransactionClassTypeID]  ,[IsVoided]  ,[VoidedDateTime]  ,[VoidedEntryID] ,[VoidedEntryClassTypeID]  ,[VoidedReason]  ,[QueryStartDateTime] ,[QueryEndDateTime]  ,[ReminderDateTime]  ,[ReminderPrompt]  ,[PartID]  ,[ActivityType]  ,[ActivityTypeText]  ,[IsBillable] ,[BillableDateTime]  ,[UseActualTime]  ,[BillingNotes]  ,[BillingType]  ,[TotalBilledTime]  ,[RecurringActivityID]  ,[LinkID]  ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID]) 
 
        VALUES
            ( @JournalID     -- (<ID, int,>
            , -1     -- ,<StoreID, int,>
            , 20510     -- ,<ClassTypeID, int,>
            , 'SQLBridge'     -- ,<ModifiedByUser, nvarchar(25),>
            , @ComputerName     -- ,<ModifiedByComputer, nvarchar(25),>
            , @DT    -- ,<ModifiedDate, datetime,>
            ,  0    -- ,<SeqID, int,>
            ,  0    -- ,<IsSystem, bit,>
            ,  1    -- ,<IsActive, bit,>
           , 10  -- <EmployeeID, int,>
           , 6  -- <JournalActivityType, int,>
           , 'Company'  -- <JournalActivityText, nvarchar(25),>
           , 'Contact Created'  -- <Description, nvarchar(50),>
           , 'Contact Inserted by SQLBridge'  -- <Notes, nvarchar(max),>
           , @DT  -- <StartDateTime, datetime,>
           , @DT  -- <EndDateTime, datetime,>
           , NULL  -- <TotalTime, datetime,>
           , NULL  -- <ScheduledDateTime, datetime,>
           , 10  -- <CompletedByID, int,>
           , @DT  -- <CompletedDateTime, datetime,>
           , 1  -- <IsSummary, bit,>
           , 1  -- <IsDetail, bit,>
           , NULL  -- <SummaryID, int,>
           , NULL  -- <SummaryClassTypeID, int,>
           , NULL  -- <SummaryAmount, decimal(18,4),>
           , NULL  -- <DetailAmount, decimal(18,4),>
           , NULL  -- <StartGLGroupID, int,>
           , NULL  -- <EndGLGroupID, int,>
           , @AccountID  -- <AccountID, int,>
           , 2000  -- <AccountClassTypeID, int,>
           , @ContactID  -- <ContactID, int,>
           , 3000  -- <ContactClassTypeID, int,>
           , NULL  -- <TransactionID, int,>
           , NULL  -- <TransactionClassTypeID, int,>
           , 0  -- <IsVoided, bit,>
           , NULL  -- <VoidedDateTime, datetime,>
           , NULL  -- <VoidedEntryID, int,>
           , NULL  -- <VoidedEntryClassTypeID, int,>
           , NULL  -- <VoidedReason, nvarchar(max),>
           , @DT  -- <QueryStartDateTime, datetime,>
           , @DT  -- <QueryEndDateTime, datetime,>
           , NULL  -- <ReminderDateTime, datetime,>
           , 0  -- <ReminderPrompt, bit,>
           , NULL  -- <PartID, int,>
           , 0  -- <ActivityType, int,>
           , NULL  -- <ActivityTypeText, nvarchar(50),>
           , 0  -- <IsBillable, bit,>
           , NULL  -- <BillableDateTime, datetime,>
           , 0  -- <UseActualTime, bit,>
           , NULL  -- <BillingNotes, nvarchar(max),>
           , 0  -- <BillingType, int,>
           , 0  -- <TotalBilledTime, float,>
           , NULL  -- <RecurringActivityID, int,>
           , NULL  -- <LinkID, int,>
           , NULL  -- <LinkStoreID, int,>
           , NULL  -- <LinkClassTypeID, int,>
           , NULL  -- <SpecialCode, nvarchar(25),>
           , @DivisionID  -- <DivisionID, int,>
           , 0  -- <HasCalendarLinks, bit,>
           , NULL  -- <TipRecipientID, int,>
           , NULL  -- <PartClassTypeID, int,>
           , NULL  -- <RecurringClassTypeID, int,>
           , NULL  -- <StationID, int,>
           , NULL  -- <StationClassTypeID, int,>
           , NULL  -- <CurrentState, int,>
           , NULL  -- <StageID, int,>
           , NULL  -- <StageClassTypeID, int,>)
        );
 
 
        --
        -- Step 5, COMMIT the transaction to the database
        --
 
        COMMIT TRANSACTION
    END TRY
 
    --
    -- Step 6, Handle ERRORS by cancelling the transaction and re-raising the error 
    --
 
    BEGIN CATCH
        ROLLBACK TRANSACTION;
 
        INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
        VALUES(
            NULL, 3000, @AccountID, 1,
            'Contact '+@FirstName+' '+@LastName+'for Account.ID '+CONVERT(VARCHAR(12), @AccountID)+' Import FAILED due to Unhandled Exception.',
            'Exception: '+ERROR_MESSAGE()
        );
 
        RETURN;
    END CATCH;
 
    --
    -- Step 7, REFRESH the Order Record to let copies of Control konw there is a new company
    --
 
    EXEC dbo.csf_chapi_refresh @AccountId, 2000, 0;
 
     -- Now Return the new Contact
    INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
    VALUES(
        @ContactID, 3000, @AccountID, 0,
        'Contact '+@FirstName+' '+@LastName+'for Account.ID '+CONVERT(VARCHAR(12), @AccountID)+' Imported.',
        ''
    );
 
    SELECT * FROM @Logs;
    RETURN;
 
END

Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+