The search page stored procedure is responsible for searching and returning the correct result set. The data table returned from this SP should have a unique list of customers. The SP should also return the total count and error messages if there are any. When you make changes to the base stored procedure, it is recommended that you save the stored procedure with a new name, as to not overwrite the base information.
All Column Names and Table names used in the Stored Procedure should be strictly written in UPPERCASE. To implement a dynamic module like Member directory, Personify has done a lot of XML parsing. Since XML is case sensitive, having Columns and Tables in any other case will lead to inconsistent results.
For a complete sample stored procedure, please see Sample Member Directory Search Page Stored Procedure.
To create the search page stored procedure:
1. The stored procedure name should start with USP_MEMBERDIRECTORY_SEARCH_
CREATE PROCEDURE [dbo].[usp_MEMBERDIRECTORY_SEARCH_XXXXX]
2. The following Input Parameters are mandatory for the SP definition. Organizations may choose not to use some of these parameters in the select criteria of the data being fetched.
@SCHEMA_ONLY BIT = 0,
@IP_ORG_ID ORG = null ,
@IP_ORG_UNIT_ID ORG = null ,
@IP_IDENTITY_MCID CUSTOMER_ID = null ,
@IP_IDENTITY_SCID SUB_SEQUENCE = null ,
@IP_IDENTITY_IP_ADDR VARCHAR(60) = null ,
@IP_START_INDEX INT = null ,
@IP_PAGE_SIZE INT = null ,
@IP_LATITUDE decimal(18,15) = null ,
@IP_LONGITUDE decimal(18,15) = null ,
@IP_SEARCH_DISTANCE int = null ,
@IP_SEARCH_TEXT FULL_NAME = null ,
@IP_MAX_COUNT_TO_FETCH INT = null ,
3. The following parameters will be used for the advanced search. Users have the option to define their own advance search parameters. These parameters have to be part of the search clause of the main query for the search to work.
@IP_NATIONAL_LEVEL1_CODE Varchar(24) = null ,
@IP_CUSTOMER_CLASS Varchar(24) = null ,
@IP_NAME_CREDENTIALS Varchar(40) = null ,
@IP_COUNTRY Varchar(24) = null ,
@IP_LANGUAGE Varchar(24) = null ,
@IP_SPECIALITY Varchar(24) = null ,
4. The following parameters are also mandatory parameters:
@OP_TOTAL_COUNT INT = null OUTPUT ,
@OP_ERROR_MESSAGE Varchar(100) = null OUTPUT
)
AS
BEGIN
5. The following section takes care of returning the schema of the table the SP returns. The select schema should 2 columns – TABLE_NAME and COLUMN_NAME. The table should contain all the columns returned by the SP.
set @OP_ERROR_MESSAGE = ''
IF @SCHEMA_ONLY = 1
BEGIN
SELECT
'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'MASTER_CUSTOMER_ID' COLUMN_NAME
union all
SELECT
'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'SUB_CUSTOMER_ID' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'CUSTOMER_CLASS' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'FIRST_NAME' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'LAST_NAME' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'LABEL_NAME' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'RECORD_TYPE' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'DISPLAY_LINE_1' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'DISPLAY_LINE_2' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'IMAGEID' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'ADDRESS_TAGS' COLUMN_NAME
union all
SELECT 'usp_MEMBERDIRECTORY_SEARCH_NATIONAL' TABLE_NAME,
'DISTANCEINMILES' COLUMN_NAME
return;
END
6. The following code is for creating the full text search string (e.g., in the full text select clause, <smith> will be formatted at "smith*", <smith va> will be formatted as "smith*" AND "va*").
DECLARE @LV_ACTUAL_SEARCH_TERM VARCHAR(255)
DECLARE @LV_Q VARCHAR(10) = '"'
DECLARE @LV_QS VARCHAR(10) = '*"'
SELECT @LV_ACTUAL_SEARCH_TERM = RTRIM(LTRIM(@IP_SEARCH_TEXT))
WHILE CHARINDEX(' ', @LV_ACTUAL_SEARCH_TERM ) > 0
BEGIN
SELECT @LV_ACTUAL_SEARCH_TERM = REPLACE(@LV_ACTUAL_SEARCH_TERM , ' ', ' ')
END
select @LV_ACTUAL_SEARCH_TERM = @LV_Q + REPLACE( @LV_ACTUAL_SEARCH_TERM , ' ' ,@LV_QS + ' AND ' + @LV_Q ) + @LV_QS
7. The following code is necessary for location based searches. If you do not want to include the location based searched, this code can be removed.
If there is no location based search code in the SP, setting the Include Location Search flag in the Personify360 screen will not work.
DECLARE @LV_PERFORM_LOCATION_SEARCH BIT = 0
DECLARE @LV_CURRENT_LOCATION geography
set @LV_PERFORM_LOCATION_SEARCH = 0
if (NOT ISNULL(@IP_LATITUDE, 0) = 0 ) AND (NOT ISNULL(@IP_LONGITUDE, 0) = 0 ) AND (NOT ISNULL(@IP_SEARCH_DISTANCE, 0) = 0 )
BEGIN
set @LV_PERFORM_LOCATION_SEARCH = 1
set @LV_CURRENT_LOCATION = geography::Point(@IP_LATITUDE, @IP_LONGITUDE, 4326)
END
8. The first query in the SP is to get the count of number of records to be returned. Organizations, depending on their requirement, can add their own conditions to this query.
select @OP_TOTAL_COUNT = COUNT(*) from dbo.FTS_CUSTOMER_INFO F
//NOTE: FOLLOING JOIN IS NOT NEEDED IF LOCATION SEARCH IS NOT A REQUIREMENT FOR THIS MEMBER DIRECTORY
LEFT JOIN CUS_ADDRESS_GEO_LOCATION CAGL ON F.CUS_ADDRESS_ID = CAGL.CUS_ADDRESS_ID
// ------------------- FULL TEXT SEARCH CLAUSE
where ( contains ( F.SEARCH_TAGS, @LV_ACTUAL_SEARCH_TERM) )
--AND F.INCLUDE_IN_WEB_MOBILE_DIRECTORY_FLAG = 1
AND F.IS_ACTIVE_FLAG = 'Y'
// -- CODE for ADVANCE FILTERS
// NOTE – IF Advance Filters are set up as single select use a simple equal operator
AND (ISNULL( @IP_CUSTOMER_CLASS ,'') = '' OR F.CUSTOMER_CLASS = @IP_CUSTOMER_CLASS)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL( @IP_LANGUAGE ,'') = '' OR F.DEFAULT_LANGUAGE_CODE = @IP_LANGUAGE)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
// IF Advance Filters are set up as multi select, use the following example
AND (ISNULL (@IP_SPECIALITY, '') ='' OR exists (select 1 from CUS_SPECIALTY CS
where CS.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID and CS.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
and exists (select 1 from
udf_SplitString(@IP_SPECIALITY, ',') CUS_SP_TBL
where CS.CUS_SPECIALTY_CODE = CUS_SP_TBL.STRING_PARTS)))
// if CHECKING FOR MEMBERSHIPS, USE THE OFFLINE LINE TABLES
AND ( exists ( select 1 from MBR_DIRECTORY_MEMBERSHIP MDM
where MDM.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and MDM.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
AND ISNULL (@IP_NATIONAL_LEVEL1_CODE, '') ='' OR MDM.LEVEL1 = @IP_NATIONAL_LEVEL1_CODE))
// USE FOLLOWING JOIN IF LOCATION SEARCH IS A REQUIREMENT
AND ((@LV_PERFORM_LOCATION_SEARCH = 0) OR ( (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192) < @IP_SEARCH_DISTANCE ))
9. If Count of records exceeds the max count configured, return and set the error message.
If (@OP_TOTAL_COUNT > @IP_MAX_COUNT_TO_FETCH)
BEGIN
set @OP_ERROR_MESSAGE = 'Count Exceeded'
--select @OP_ERROR_MESSAGE
return
END
10. The following is the main SQL to get the data. The result set should have a unique list of customer records. The where clause for this will be exactly the same as the where clause for the count statement.
select
F.MASTER_CUSTOMER_ID
, F.SUB_CUSTOMER_ID
, F.CUSTOMER_CLASS
, F.FIRST_NAME
, F.LAST_NAME
, F.LABEL_NAME
, F.RECORD_TYPE
, F.DISPLAY_LINE_1
, F.DISPLAY_LINE_2
, F.IMAGEID
, ADDRESS_TAGS
, CASE @LV_PERFORM_LOCATION_SEARCH
WHEN 1 THEN
ROUND (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192 ,2)
ELSE
0
END As DISTANCEINMILES
, @LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192 as NEW
from dbo.FTS_CUSTOMER_INFO F
LEFT JOIN CUS_ADDRESS_GEO_LOCATION CAGL ON F.CUS_ADDRESS_ID = CAGL.CUS_ADDRESS_ID
where (contains ( F.SEARCH_TAGS, @LV_ACTUAL_SEARCH_TERM) )
--AND F.INCLUDE_IN_WEB_MOBILE_DIRECTORY_FLAG = 1
AND F.IS_ACTIVE_FLAG = 'Y'
AND (ISNULL( @IP_CUSTOMER_CLASS ,'') = '' OR F.CUSTOMER_CLASS = @IP_CUSTOMER_CLASS)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL( @IP_LANGUAGE ,'') = '' OR F.DEFAULT_LANGUAGE_CODE = @IP_LANGUAGE)
AND (ISNULL( @IP_COUNTRY ,'') = '' OR F.COUNTRY_CODE = @IP_COUNTRY)
AND (ISNULL (@IP_SPECIALITY, '') ='' OR exists ( select 1 from CUS_SPECIALTY CS
where CS.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and CS.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
and exists (select 1 from
udf_SplitString(@IP_SPECIALITY, ',') CUS_SP_TBL
where CS.CUS_SPECIALTY_CODE = CUS_SP_TBL.STRING_PARTS)))
-- Uncomment when there is valid data
AND ( exists ( select 1 from MBR_DIRECTORY_MEMBERSHIP MDM
where MDM.MASTER_CUSTOMER_ID = F.MASTER_CUSTOMER_ID
and MDM.SUB_CUSTOMER_ID = F.SUB_CUSTOMER_ID
AND ISNULL (@IP_NATIONAL_LEVEL1_CODE, '') ='' OR MDM.LEVEL1 = @IP_NATIONAL_LEVEL1_CODE))
AND ((@LV_PERFORM_LOCATION_SEARCH = 0) OR ( (@LV_CURRENT_LOCATION.STDistance(CAGL.GEO_LOCATION) * 0.000621371192) < @IP_SEARCH_DISTANCE ))
ORDER BY F.LABEL_NAME
OFFSET @IP_START_INDEX ROWS
FETCH NEXT @IP_PAGE_SIZE ROWS ONLY
END
END