Amending the SP in SSMS

Last updated:

    Overview

    In the previous section, "Import the PDF into CCH Insolvency", we created a skeleton stored procedure ("SP").  This section describes how to open and amend the SP in SQL Server Management Studio ("SSMS").

    Amending the SP in SSMS

    Now the basic Stored Procedure has been created, we now need to import the draft SP into SSMS to make any final adjustments, such as:

    • Reformatting the entered data
    • Combining or splitting fields
    • Appending text to a field

    In this example, we will continue work on the S120 notice.

    1.    Launch SSMS and select File > Open > File:

    File:test/insolvency/230_21+PDF+templates/070_Open+the+SP+in+SSMS/PDF_OpenSSMS.png

    2.    Navigate to where you saved the draft SP above, select the file and click [Open].

    The SP is opened in SSMS, which will look similar to that shown in Listing 1 below:

    IF EXISTS ( SELECT  *
                FROM    sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[pdfsp_S120_notice.sql]')
                        AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
        DROP PROCEDURE [dbo].[pdfsp_S120_notice.sql]
    GO
    
    SET QUOTED_IDENTIFIER  ON
    SET ANSI_NULLS  ON
    GO
    
    CREATE PROCEDURE dbo.pdfsp_S120_notice.sql
        @AdminID INT
      , @SignatoryInClause NVARCHAR(MAX) = NULL
      , @Company_Contact NVARCHAR(MAX) = NULL
      , @Name_Pension_Scheme NVARCHAR(MAX) = NULL
      , @Pension_Scheme_Number NVARCHAR(MAX) = NULL
    
    AS
    
        SET NOCOUNT ON
    
        CREATE TABLE dbo.#Data ( ID INT NOT NULL )
        IF NOT @SignatoryInClause IS NULL
            EXEC dbo.usp_Split @SignatoryInClause
    
        DECLARE @Data TABLE
            (
              Title NVARCHAR(200)
            , Value NVARCHAR(MAX)
            )
    
            --Return results
        SELECT  *
        FROM    @Data
    
        SET NOCOUNT OFF
    
        RETURN @@ERROR
    GO
    
    SET QUOTED_IDENTIFIER  OFF
    SET ANSI_NULLS  ON
    GO
    
    GRANT  EXECUTE  ON [dbo].[pdfsp_S120_notice.sql]  TO [insol2_users]
    GO
    

    You should note that this listing has been formatted to improve readability: your draft SP may appear differently but should contain the same information.

    The following points should also be noted:

    • The data is then returned by selecting all the table entries in the table:
    SELECT *
    FROM @Data
    
    • The section
    CREATE TABLE dbo.#Data ( ID INT NOT NULL )
    IF NOT @SignatoryInClause IS NULL
        EXEC dbo.usp_Split @SignatoryInClause
    

    has been added to the draft SP because the User Prompt ‘Select One Signatory Only’ was selected above.  This will, of course, change depending on which User Prompts have been selected prior to creating the draft SP.

    • The variable @SignatoryInClause NVARCHAR(MAX) = NULL  to hold the signatory’s name has also been added.
    • The section to obtain the Signatory’s details has also been added:
    CREATE TABLE dbo.#Data ( ID INT NOT NULL )
    IF NOT @SignatoryInClause IS NULL
    EXEC dbo.usp_Split @SignatoryInClause
    
    • The data to be displayed in the pdf is held in a table, @Data.  Data to be manually or programmatically entered into the pdf is entered in pairs: the first element being the data, the second being the field name in the pdf. 
    • A variable for the Court name, @NameOfCourt NVARCHAR(MAX) = NULL, has also been added.

    3.    We now need to create the various SQL clauses to hold the data from the Document Production Wizard:

    • The contact at the insolvent company (@Company_Contact )
    • The name of the company’s pension scheme (@Name_Pension_Scheme )
    • The number of the pension scheme (@Pension_Scheme_Number )

    The following listing shows the entries to be created:

    INSERT INTO @Data
    VALUES ('CoContactName', @Company_Contact)
    
    INSERT INTO @Data
    VALUES ('NameOfPensionScheme', @Name_Pension_Scheme)
    
    INSERT INTO @Data
    VALUES ('PensNumber', @Pension_Scheme_Number)
    

    This is all that is required to connect an entry in the Document Production Wizard to the form field in the pdf.

    Related