Here’s an example of using the “HTML From Stored Procedure” module to display our “Next Step” information at the top of the person detail page.
As you can imagine, the HTML displayed varies depending on the person you look at. This is possible because when Arena renders an “HTML From Stored Procedure” or a “Report Grid From Query” module, it will look at the defined parameters and replace any values with the value of any querystring parameter with the same name.
Because the person detail page always includes a “guid” query string value…
we were able to create a stored procedure with a @guid parameter that returned HTML specific to the selected person.
When specifying the parameters in your module settings, it’s important to keep in mind that Arena does a case-sensitive compare of parameter names before replacing values. Since “guid” is always lower-case in the URL, we needed to make sure we used lower-case in the Parameters module setting…
For reference here’s the stored procedure we used to display the next step information
ALTER PROC cust_sp_html_personNextClass @Guid uniqueidentifier AS DECLARE @PersonID int DECLARE @NickName varchar(50) DECLARE @Gender varchar(8) DECLARE @ClassLevel varchar(10) DECLARE @HtmlMsgPtr binary(16) DECLARE @TempMessage varchar(8000) CREATE TABLE #HtmlTable ([html] text) INSERT INTO #HtmlTable VALUES ('') SELECT @HtmlMsgPtr = TEXTPTR([html]) FROM #HtmlTable SELECT @PersonID = person_id, @NickName = nick_name, @Gender = CASE gender WHEN 0 THEN 'He' WHEN 1 THEN 'She' ELSE 'He/she' END FROM core_person WHERE guid = @Guid AND dbo.fn_age(birth_date, GETDATE()) > 13 AND record_status = 0 IF @PersonID IS NOT NULL BEGIN SET @ClassLevel = LEFT([dbo].[core_funct_class_level](@PersonID),3) IF @ClassLevel <> '300' BEGIN DECLARE @ParentProfileID int IF @ClassLevel = '' SET @ParentProfileID = 8479 IF @ClassLevel = '100' SET @ParentProfileID = 8480 IF @ClassLevel = '200' SET @ParentProfileID = 8481 IF @ParentProfileID IS NOT NULL BEGIN DECLARE @ProfileName varchar(100) DECLARE @ProfileID int DECLARE @Start datetime SELECT TOP 1 @ProfileName = PP.profile_name, @ProfileID = PE.profile_id, @Start = PE.start FROM evnt_event_profile PE INNER JOIN core_profile P ON P.profile_id = PE.profile_id INNER JOIN core_profile PP ON PP.profile_id = P.parent_profile_id WHERE P.parent_profile_id = @ParentProfileID AND PE.start > GETDATE() ORDER BY PE.start ASC IF @ProfileID IS NOT NULL BEGIN SET @TempMessage = '<div style="color:black; font-size:.9em;padding:10px">' + @NickName + ' has not yet attended any <span style="font-weight:bold">' + @ProfileName + '</span>. ' + @Gender + ' could attend the <a href="default.aspx?page=376&profile=' + CAST(@ProfileID AS varchar) + '">next class</a> on ' + CONVERT(varchar, @Start, 1) + '.</div>' UPDATETEXT #HtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage END END END END SELECT [html] FROM #HtmlTable DROP TABLE #HtmlTable
Nice article! keep it up.