Amending the SP in SSMS

Last modified

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

Page statistics
283 view(s) and 10 edit(s)
Social share
Share this page?

Tags

CCH Insolvency

Comments

Attachments

 

 | Cookie Policy | Copyright | Privacy Policy Terms of Use | Contact Us |