A Stored Procedure is a collection of SQL statements and optional control-of-flow logic that are compiled and stored in the database, for example, if A then do this, if B, then do something else. Stored Procedures can be used for a variety of purposes: fetching sets of data, reading single values, and inserting/updating/deleting data in many tables.

They are re-usable, can take and return parameters, and can return information relative to their successful or failed execution. Software applications that run on top of relational databases often make use of Stored Procedures to handle certain complex database operations.

TIBCO Scribe® Insight allows you to expose or hide tables, views, and Stored Procedures using the KSYNCTABLES view. This view queries the database system tables and the Scribe KSYNCTABLEBASE table to generate a list of Tables, Views, or Stored Procedures available within the DTS.

The KSYNCTABLEBASE table does not need to be populated unless you are trying to control which objects are displayed in the DTS. If there are certain database objects you want to hide from the user, then you can either modify the KSYNCTABLES view or insert an entry into the KSYNCTABLEBASE table and set the HIDDEN field to ‘Y’. If you want to expose a Stored Procedure within the DTS, insert an entry into the KSYNCTABLEBASE table and set the HIDDEN field to ‘N’.

The SQL scripts should not be run against the ScribeInternal database. The Stored Procedures you need to expose should be in a different database than ScribeInternal; the Stored Procedures, the required table, and view must all be located in the same database.

Exposing The Stored Procedures

Download the Exposing Stored Procedures in Scribe.ZIP file attached to this article, which contains sample files to help you with this process. Extract the files to a folder on your hard drive.

1. If your Stored Procedure does not already exist, create the Stored Procedure in the appropriate database, not ScribeInternal.
2. Run the appropriate SQL Server or Oracle script for creating the KSYNCTABLEBASE table and the KSYNCTABLES view. Sample scripts are included in the Exposing Stored Procedures in Scribe.ZIP file. Below is a SQL Server example:

3. Insert an entry in the KSYNCTABLESBASE that identifies the Stored Procedure you wish to expose, and set the HIDDEN field to ‘N’. For example, to expose the Stored Procedure “MigrateCustomers”, execute the following SQL statement against the KSYNCTABLESBASE table created above :

4. Create an ODBC DSN and a Connection in the Scribe Insight Workbench to the database that contains the Stored Procedure. For Oracle, make sure to log in using the schema owner. At this point the Stored Procedure is exposed via the ODBC Connection in Scribe Insight and can be selected as a Source data object or as a Target Execute step.

   
One reason for using the KSYNCTABLEBASE table is to control which Stored Procedures are displayed in the Workbench. This is useful if there are many Stored Procedures and you do not want to list them all.
 
It is possible to eliminate the KSYNCTABLEBASE table, which must be manually maintained, and dynamically create the list using just the KSYNCTABLES view as follows:

CREATE view [dbo].[KSYNCTABLES]
as
    select
        o.name  as TABLENAME,
        'DBO' AS OWNER,
        null as SUBJECT_AREA,
        O.NAME AS LABEL,
        o.type as TYPE,
        'Y' as UPDATABLE,
        'N' as HIDDEN,
        null as DRS_OBJECT,
        null AS BASE_TABLES,
        NULL AS REMARKS,
        NULL AS FIELD_INFO
    from dbo.sysobjects o
    where o.type = 'U'
        or o.type = 'V'
        or o.type = 'P'
       ---------------U will return tables.
       ---------------P will return Stored Procedures
       ---------------V will return views.
   

Databases With Existing KSYNC Tables

For some Microsoft Dynamics products, such as Dynamics GP, the KSYNCTABLES view and the KSYNCTABLEBASE table have been created by Scribe for the Dynamics package being supported and should not be deleted. In these situations, do not run the SQL script shown above that creates these objects; instead, add an entry to the existing KSYNCTABLEBASE table to expose your Stored Procedure.

This example shows the existing KSYNCTABLES view in Dynamics GP for SQL Server, and the insert statement required to expose a Stored Procedure. It sets the Stored Procedure’s HIDDEN field to ‘N’.
Insert Into [dbo].[KSYNCTABLESBASE] ([TABLENAME], [OWNER], [TYPE], [UPDATABLE], [HIDDEN]) Values('AAdtaValidateStats','dbo','P','Y','N');
   

Using A Stored Procedure As A Target Execute Step

1. Create the Stored Procedure specifying input and output parameters.

 

2. Create the DTS and add the Stored Procedure as an Execute step.

 

For additional information, see the following in TIBCO Scribe® Insight Help: Using Stored Procedures, Selecting A Single Data Object As The Source

Stored Procedures can be used for a variety of purposes: fetching sets of data, reading single values, and inserting/updating/deleting data in many tables. Use these instructions to expose Stored Procedures.