Nov
6

Dynamic Parameter Values

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.

image

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…

Person Detail URL

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…

image

 

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
Posted by David Turner Arena Subscribe to RSS feed

1 Comment on Dynamic Parameter Values

  1. Posizionamento su Google says:
    June 21, 2009 at 2:13 pm

    Nice article! keep it up.

Leave a Reply

*