Adding two fields plus text

Last updated:


    This section shows you how to add two fields together and include some text.

    Adding two fields plus text

    The latest forms for registering a charge at Companies House include a section for the ‘short particulars’ of the property:


    This could include the asset description and/or the asset title.  The listing below shows how this data may be presented:

    SELECT 'Short_Particulars',
       CASE WHEN ISNULL(Asset_Title, '') = ''
            THEN 'Description and Address: ' + CHAR(13) + Asset_Desc
            ELSE 'Particulars of Title: ' + CHAR(13) + Asset_Title + CHAR(13) + CHAR(13) +
                 'Description and Address: ' + CHAR(13) + Asset_Desc
    FROM @SecCredData


    The table variable @SecCredData must also be declared and populated from the charge data held.

    DECLARE @SecCredData TABLE
      Admin_ID INT ,
      Contact_Title NVARCHAR(50) ,
      Contact_FirstName NVARCHAR(75) ,
      Contact_LastName NVARCHAR(75) ,
      Sec_Cred NVARCHAR(1000) ,
      Cred_Addr NVARCHAR(1000) ,
      Cred_City NVARCHAR(100) ,
      Cred_State NVARCHAR(100) ,
      Cred_PCode NVARCHAR(50) ,
      Instruction_Date DATETIME ,
      Lodge_Date DATETIME ,
      Charge_Description NVARCHAR(1000) ,
      Asset_Desc NVARCHAR(max) ,
      Asset_Title NVARCHAR(max)
    INSERT  INTO @SecCredData
    SELECT a.AdminID ,
            con.Title ,
            con.FirstName ,
            con.LastName ,
            cp.Name ,
            ad.Address ,
            ad.City ,
            ad.State ,
            ad.PostCode ,          
            aam.InstDate ,          -- Date Charge Created
            aam.LodgeDate ,         -- Date Reg'd at Companies House
            aam.InstDescription ,
            ass.TitleDetails ,
    FROM   SecurityAsset seca
            INNER JOIN Asset ass ON seca.AssetID = ass.AssetID
            INNER JOIN Security sec ON seca.SecurityID = sec.SecurityID
            INNER JOIN Admin a
            INNER JOIN AdminAppointmentMethod aam ON a.AdminAppointmentMethodID = aam.AdminAppointmentMethodID
            INNER JOIN Creditor c ON a.AdminID = c.AdminID
            INNER JOIN Address ad ON c.AddressID = ad.AddressID
            INNER JOIN Counterparty cp ON c.CounterpartyID = cp.CounterpartyID
            INNER JOIN Contact con ON cp.CounterpartyID = con.CounterpartyID ON sec.CreditorID = c.CreditorID
    WHERE   ( ass.AdminID = @AdminID )