Table of Contents

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.

Overview

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 provide 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 SQLBridge 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.

Example Usage

This inserts a basic contact.

code_formatsql

EXEC csp_ImportContact

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

code

This inserts a sample contact with

* a different office phone number,

code_formatsql

EXEC csp_ImportContact

  1. -
  1. - All calls must supply *one* and only one of the following values
  1. -
      @AccountID = 10445,
      @CompanyName = NULL, -- The company name.  Must match EXACTLY and be the only one
  1. - Contact Name
      @FirstName = 'Sara',
      @Position  = NULL,
      @EmailAddress  = NULL,
      @IsPrimaryContact = 1,
      @IsBillingContact = 1,
  1. - Office Phone
      @UseDefaultPhone = 1,
      @WorkPhoneAC = NULL,
      @WorkPhoneNumber = NULL,
  1. - Contact Additional Phone #1
      @AddCellPhone = 1, -- Set to 1 to Add a Cell Phone to the contact
      @CellPhoneAC = '318',
      @CellPhoneNumber = '9019392',
  1. - 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 = ''

code

Stored Procedure

code_formatsql

–

– 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,
  1. - Office Phone #1
  @UseDefaultPhone  BIT = 1,
  @WorkPhoneAC      VARCHAR(10) = NULL,
  @WorkPhoneNumber  VARCHAR(25) = NULL,
  1. - 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,
  1. - 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) = '',
  1. - 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,
  1. - 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;
  1. -
  1. - Step 0. Validate the Input
  1. -
  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;
  1. - Company info Pulled from Store or looked up
  DECLARE @PhoneCountryCode   VARCHAR(10);
  DECLARE @DivisionID         INT;
  DECLARE @SalespersonID      INT;
  1. - Retrieve Defaults Information from databases
  SELECT  TOP 1
          @PhoneCountryCode = COALESCE(DefaultCountryCode, '1')
  FROM Store
  WHERE ID > 0
  ORDER BY ID;
  1. - 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
  1. - Load some default information
  SELECT @DivisionID = DivisionID,
         @SalespersonID = SalespersonID1,
         @ShippingAddressID = ShippingAddressID,
         @BillingAddressID = BillingAddressID
  FROM Account
  WHERE ID = @AccountID;
  1. - 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
  1. -
  1. - Step 1, Create the associated CONTACT RECORDS
  1. -
  1. - 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  -- 
      , -1     -- ,
      , 3000     -- ,
      , 'SQLBridge'     -- ,
      , @ComputerName     -- ,
      , @DT          -- ,
      , 0    -- ,
      , 0    -- ,
      , 1    -- ,
      , NULL  -- 
      , @FirstName  -- 
      , @LastName  -- 
      , @Title  -- 
      , @POSITION  -- 
      , @EmailAddress  -- 
      , @PhoneNumberID+0  -- 
      , CASE WHEN (@AddCellPhone = 1) THEN @PhoneNumberID+2 ELSE NULL END  -- 
      , @AccountID  -- 
      , @IsPrimaryContact  -- 
      , @IsBillingContact  -- 
      , @BillingAddressID  -- 
      , @ShippingAddressID  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , @SalespersonID  -- 
      , NULL  -- 
      , NULL  -- 
      , 1  -- 
      , 1  -- 
      , ''  -- 
      , NULL  -- 
      , 1  -- 
      , '('+@WorkPhoneAC+') '+@WorkPhoneNumber  -- 
      , @WorkPhoneType  -- 
      , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- 
      , CASE WHEN (@AddCellPhone = 1) THEN '('+@CellPhoneAC+') '+@CellPhoneNumber ELSE '' END  -- 
      , @CellPhoneType  -- 
      , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- 
      , NULL  -- 
      , 0  -- 
      , 1  -- 
      , NULL  -- 
      , NULL  -- 
      , ''  -- 
      , 0  -- 
      , 0  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , NULL  -- 
      , 0  -- 
      , NULL  -- 
      , -1  -- 
      , -1  -- 
      , NULL  -- 
      , NULL  -- 
      , 0  -- 
      );
  1. - Insert ContactUDF (Nothing by default)
      INSERT INTO [dbo].[AccountContactUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ) 
      VALUES
      ( @ContactID  -- 
      , -1     -- ,
      , 3001     -- ,
      , 'SQLBridge'     -- ,
      , @ComputerName     -- ,
      , @DT  -- ,
      , 0    -- ,
      , 0    -- ,
      , 1    -- ,
      );
  1. - 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   -- 
          , -1           -- ,
          , 4001         -- ,
          , 'SQLBridge'  -- ,
          , @ComputerName -- ,
          , @DT    -- ,
          , 0            -- ,
          , 0            -- ,
          , 1            -- ,
          , @BillingAddress1   -- 
          , @BillingAddress2   -- 
          , @BillingCity   -- 
          , @BillingState   -- 
          , ''   -- 
          , @ShippingPostalCode   -- 
          , 'US'   -- 
          , @BillingAddress1  + @NewLine
          + (CASE WHEN len(COALESCE(@BillingAddress2, '')) > 1 THEN @BillingAddress2 + @NewLine ELSE '' END) 
          + @BillingCity + ', ' + @BillingState + '  ' + @BillingPostalCode -- 
          , NULL   -- 
          , 0   -- 
          , NULL   -- 
          , 0   -- 
          , NULL   -- 
          );
      END;
  1. - 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   -- 
          , -1           -- ,
          , 4001         -- ,
          , 'SQLBridge'  -- ,
          , @ComputerName -- ,
          , @DT    -- ,
          , 0            -- ,
          , 0            -- ,
          , 1            -- ,
          , @ShippingAddress1   -- 
          , @ShippingAddress2   -- 
          , @ShippingCity   -- 
          , @ShippingState   -- 
          , ''   -- 
          , @ShippingPostalCode   -- 
          , 'US'   -- 
          , @ShippingAddress1  + @NewLine
          + (CASE WHEN len(COALESCE(@ShippingAddress2, '')) > 1 THEN @ShippingAddress2 + @NewLine ELSE '' END) 
          + @ShippingCity + ', ' + @ShippingState + '  ' + @ShippingPostalCode -- 
          , NULL   -- 
          , 0   -- 
          , NULL   -- 
          , 0   -- 
          , NULL   -- 
          );
      END;
  1. - 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   -- 
      , -1           -- ,
      , 4002         -- ,
      , 'SQLBridge'  -- ,
      , @ComputerName -- ,
      , @DT          -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , (case when @UseCompanyBillingAddress=1 then 0 else 1 end)   -- 
      , @ContactID   -- 
      , 3000   -- 
      , 10   -- Shipping Address -- 
      , @BillingAddressID   -- 
      , 'Billing'   -- 
      , 0   -- 
      , NULL   -- 
      , NULL   -- 
      );
      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   -- 
      , -1           -- ,
      , 4002         -- ,
      , 'SQLBridge'  -- ,
      , @ComputerName -- ,
      , @DT          -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , (case when @UseCompanyShippingAddress=1 then 0 else 1 end )   -- 
      , @ContactID   -- 
      , 3000   -- 
      , 11   -- Shipping Address -- 
      , @ShippingAddressID   -- 
      , 'Shipping'   -- 
      , 0   -- 
      , NULL   -- 
      , NULL   -- 
      );
  1. - 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 -- 
      , -1           -- ,
      , 4100         -- ,
      , 'SQLBridge'  -- ,
      , @ComputerName -- ,
      , @DT          -- ,
      , 0            -- ,
      , 0            -- ,
      , 1            -- ,
      , @ContactID   -- 
      , -1     -- 
      , 3000   -- 
      , @WorkPhoneType   -- 
      , @PhoneCountryCode  -- 
      , @WorkPhoneAC   -- 
      , @WorkPhoneNumber   -- 
      , ''   -- 
      , '('+@WorkPhoneAC+') '+@WorkPhoneNumber   -- 
      , 0   -- 
      , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- 
      );
      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 -- 
          , -1           -- ,
          , 4100         -- ,
          , 'SQLBridge'  -- ,
          , @ComputerName -- ,
          , GetDate()    -- ,
          , 0            -- ,
          , 0            -- ,
          , 1            -- ,
          , @ContactID   -- 
          , -1     -- 
          , 3000   -- 
          , @CellPhoneType   -- 
          , @PhoneCountryCode  -- 
          , @CellPhoneAC   -- 
          , @CellPhoneNumber   -- 
          , ''   -- 
          , '('+@CellPhoneAC+') '+@CellPhoneNumber   -- 
          , 0   -- 
          , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- 
          );
      END; -- of adding cell phone
  1. -
  1. - Step 4, If they are setting this as the Primary or Billing Contact, we need to adjust the existings ones
  1. -
      IF (@IsPrimaryContact =1)
      BEGIN
  1. - Store the Old Primary
          DECLARE @OldPrimaryContactID INT = (SELECT PrimaryContactID from Account Where ID = @AccountID);
  1. - Update the Customer Record
          UPDATE Account
          SET SeqID = SeqID + 1, PrimaryContactID = @ContactID
          WHERE ID = @AccountID;
  1. - 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
  1. - Store the Old Primary
          DECLARE @OldBillingContactID INT = (SELECT AccountingContactID from Account Where ID = @AccountID);
  1. - Update the Customer Record
          UPDATE Account
          SET SeqID = SeqID + 1, AccountingContactID = @ContactID
          WHERE ID = @AccountID;
  1. - And se thte Old Contact ID so it is not primary
          UPDATE AccountContact
          SET SeqID = SeqID + 1, IsAccountingContact = 0
          WHERE ID = @OldPrimaryContactID;        
      END;
  1. -
  1. - Step 5, Create a note in the JOURNAL record
  1. -
      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     -- (
          , -1     -- ,
          , 20510     -- ,
          , 'SQLBridge'     -- ,
          , @ComputerName     -- ,
          , @DT    -- ,
          ,  0    -- ,
          ,  0    -- ,
          ,  1    -- ,
         , 10  -- 
         , 6  -- 
         , 'Company'  -- 
         , 'Contact Created'  -- 
         , 'Contact Inserted by SQLBridge'  -- 
         , @DT  -- 
         , @DT  -- 
         , NULL  -- 
         , NULL  -- 
         , 10  -- 
         , @DT  -- 
         , 1  -- 
         , 1  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , @AccountID  -- 
         , 2000  -- 
         , @ContactID  -- 
         , 3000  -- 
         , NULL  -- 
         , NULL  -- 
         , 0  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , @DT  -- 
         , @DT  -- 
         , NULL  -- 
         , 0  -- 
         , NULL  -- 
         , 0  -- 
         , NULL  -- 
         , 0  -- 
         , NULL  -- 
         , 0  -- 
         , NULL  -- 
         , 0  -- 
         , 0  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , @DivisionID  -- 
         , 0  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- 
         , NULL  -- )
      );
  1. -
  1. - Step 5, COMMIT the transaction to the database
  1. -
      COMMIT TRANSACTION
  END TRY
  1. -
  1. - Step 6, Handle ERRORS by cancelling the transaction and re-raising the error
  1. -
  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;
  1. -
  1. - Step 7, REFRESH the Order Record to let copies of Control konw there is a new company
  1. -
  EXEC dbo.csf_chapi_refresh @AccountId, 2000, 0;
  1. - 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

code

Source

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+

See Also