HTML from Stored Procedures

 

This is an extremely powerful module that allows you to display HTML content on your webpage based upon your data. This module uses SQL queries to automatically pull in fields of data based upon your specifications and the existing information on the page.

You can display something as simple and specific as a users name on the welcome page when they sign in, or something as complex and broad as displaying a monthly chart of attendance for a specific group. All the information displayed can be tied to the specific person who has signed in.

Requirements

Access to Arena Administration

Access to SQL Server Management Studio 2008r2 through 2016

 

Setup

First, you must be familiar with Stored Procedures. A Stored Procedure is a set of Structured Query Language (SQL) statements with an assigned name that is stored in the database in a compiled form so that it can be shared by a number of programs. In Arena, these are used to automatically process and display data on a webpage. Stored procedures are the backbone of Arena. Almost any action taken within the Arena program runs stored procedures automatically in order to get the content requested to appear on the page.

Below are the steps for setting up this process:

1          Determine what you want to accomplish and where you want this information to be displayed on your website. For example, Display the users name on the Homepage after they log in.

2.          Build the Stored Procedure in SQL Server Management Studio.

3.          Add the HTML code from the Stored Procedure module on to the desired page.

4.          Configure the settings of the Stored Procedures including Parameters.

5.          Test

 

Examples that you may want to consider for using this module include: Displaying group, family, contribution, attendance, or tag information specific to the person logged in.

You can display who the group leader is, display a picture of the group leader, display a short summary of giving information, attendance history information, volunteer opportunities available to the logged in user, and events that cater directly to the logged in user. For example, you could display information based on a logged in users age group or other criteria that you determine such as tags or preferences, martial status, graduation date, etc.

 

Application

The following example shows the steps taken to create a Stored Procedure that displays the logged in users name on the Home Page.

 

Build the Stored Procedure in SQL Server Management Studio

1.          After opening SQL Server Management Studio, locate your database.

2.          Right-click on your database and select the option New Query.

3.          Create a stored procedure. (Enter the text of the stored procedure.)

NOTE: Your stored procedure must include the parameter of "@OrganizationID INT". As of version 2011.2.100, Arena passes this value when executing a stored procedure. Also, your stored procedure must return a single string that is labeled [html].

4.          Click the Execute button to create the stored procedure. (If you need to make changes later, change the word "CREATE" at the beginning of the stored procedure to "ALTER" then execute again to save the changes. The result must have the html column in all lowercase.)

Add the HTML from Stored Procedure module on to the desired page

1.          From the Administration menu, select the Pages option.

2.          For this example we are using the Members Responsive Homepage. Select the "Homepage" folder under the Page Hierarchy tree view.

3.          Click the Main Content tab.

4.          Click the Add link on the Main Content tab.

5.          Click the New Module link when it appears.

6.          Change the Type of Module from "Advance Html Text" to "HTML From Stored Procedure".

7.          Change the Title field to describe the purpose of this module. For this example, enter "Display Welcome" in the Title field.

 

Configure the settings of the Stored Procedures including Parameters

1.          From the Main Content tab, notice the Settings section. This is where you configure the settings of the stored procedure.

2.          Include Current User ID - Set this to "True". This will pass the "@CurrentUser" parameter to the stored procedure without having to specify it under Parameters.

NOTE: If you have additional parameters, add them to the Parameters box.

3.          Enter the stored procedure you wish to use in the Stored Procedure box.

NOTE: The "@OrganizationId" is automatically passed to the stored procedure. You do not need to specify it in parameters.

4.          Click the  button.

 

Test

1.          Open a new tab on your browser or a new browser window and navigate to your members responsive site. Enter your Username and Password and then click the Login button.

2.          Verify that the user's name displays on the Homepage.

 

3.          You are finished! Click the  button to log out.