Using Procedure in Events

There are two types of procedures, SQLDDL and SQLPROC. SQLDDL should return a SQL that runs when the procedure is completed, commonly used to return SQL creating temp tables. SQLPROC is used, but does not necessarily return any values. These procedures must be defined in the Event right panel in Template Design.

This topic contains the following sections:

• SQLDDL Example
• SQLPROC Example
• #pane

SQLDDL Example

Below is an example of SQLDDL:

CREATEPROCEDURE [cust].[p_IS2010_TRANS_initiate]

asbegin

 declare @sql varchar(500)

  set @sql =

 'CREATE TABLE #ledgertrans('+

 '[id] [uniqueidentifier] NULL,'+

 '[account] [nvarchar](4)  COLLATE DATABASE_DEFAULT NOT NULL,'+

 '[unit] [nvarchar](2)  COLLATE DATABASE_DEFAULT NOT NULL,'+

 '[voucherdate] [nvarchar](8)  COLLATE DATABASE_DEFAULT NOT NULL,'+

 '[amount] [decimal](18, 2)  NOT NULL,'+

 '[docid] [nvarchar](255)  COLLATE DATABASE_DEFAULT NULL'+

 ')'

 select @sql

end

SQLPROC Example

Below is an example of SQLPROC:

CREATEPROCEDURE [cust].[p_datastore_update]

    (

@client_id varchar(50),

           @user_id  NVARCHAR(50)

    )

ASbegin

insertnINTO #data_cells select*from BIZVIEW_DEMO.cust.v_1_data_cells_demo where CLIENT ='1'and ACCOUNT BETWEEN'3010'AND'5080'AND TIMEVAL ='201011'

end

Using SQLDDL to create temp tables is only useful in #before_document _creation (see image above).

#pane

Before a SQLPROC is executed a temp table #pane is created, containing given conditions, as wells as fixed parameters to generate the report/form.

This temp table can be used by the procedure to make accurate searches in the database.