Tutorial -1
1. Sample procedure to updating the record
CREATE DEFINER=`root`@`localhost` PROCEDURE `archive_affiliate_user`(
IN `affilaite_id_csv` LONGTEXT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET @s1 = CONCAT('UPDATE affiliates set isactive= ''0'', modified_on = NOW()
WHERE affiliate_id IN(',"'",REPLACE(affilaite_id_csv,',',"','"),"')");
PREPARE stmt1 FROM @s1;
EXECUTE stmt1;
SET @s2 = CONCAT("UPDATE users SET isactive= '0', modified_on = NOW()
WHERE user_id IN (SELECT user_id FROM user_affiliates
WHERE affiliate_id IN('",REPLACE(affilaite_id_csv,',',"','"),"'))");
PREPARE stmt2 FROM @s2;
EXECUTE stmt2;
COMMIT;
SELECT @s2;
END
2. Creating temporary table to write the join query
CREATE DEFINER=`root`@`localhost` PROCEDURE `reports`(
IN `p_usr_id` VARCHAR(50), IN `role_id` VARCHAR(50),
IN `p_mailing_filter` VARCHAR(50), IN `p_template_filter` VARCHAR(50),
IN `p_filters` VARCHAR(50),
IN `p_aff` VARCHAR(50), IN `p_tmp` VARCHAR(50), IN `p_usr` VARCHAR(50),
IN `frm_date` VARCHAR(50), IN `to_date` VARCHAR(50),
IN `p_limit` VARCHAR(50), IN `sort` VARCHAR(50),
IN `page` VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
#------------------Local Variables Declaration-------------------------
DECLARE v_aff,v_tmp,v_usr,v_fdate,v_tdate,v_role_id,v_mailing_filter,v_template_filter,v_limit,v_sort,v_all_filters varchar(250) default '';
DECLARE v_cnt int(11) default 0;
DECLARE v_frdate varchar(10) default DATE_SUB(CURDATE(), INTERVAL 6 DAY);
DECLARE v_todate varchar(10) default CURDATE();
#-----------------Drop temporary tables before creation----------------
DROP TABLE IF EXISTS camp_users;
DROP TABLE IF EXISTS issue_events;
#-----------------Create Temporary Tables------------------------------
CREATE TEMPORARY TABLE camp_users (
campaign_id INT(11) NOT NULL DEFAULT '0',
mailing_status ENUM('w','r','a') NOT NULL DEFAULT 'w' ,
affiliate_name VARCHAR(50) NOT NULL,
campaign_name VARCHAR(150) NOT NULL,
template_id INT(11) NOT NULL,
template_name VARCHAR(150) NOT NULL,
time_sent DATETIME NOT NULL,
affiliate_id INT(15) NOT NULL,
recipients_count INT(11) UNSIGNED NOT NULL DEFAULT '0',
suppressed INT(11) NULL DEFAULT NULL,
user_id INT(11) NOT NULL DEFAULT '0',
user_name VARCHAR(50) NOT NULL,
INDEX campaign_id (campaign_id),
INDEX template_id (template_id),
INDEX affiliate_id (affiliate_id),
INDEX user_id (user_id)
);
CREATE TEMPORARY TABLE issue_events (
campaign_id INT(11) NULL DEFAULT NULL,
issue_id VARCHAR(15) NOT NULL,
issue_id_pk INT(11) NOT NULL DEFAULT '0',
unsubscribes INT(11) NULL DEFAULT NULL,
opens INT(11) NULL DEFAULT NULL,
sends BIGINT(21) NOT NULL DEFAULT '0',
received BIGINT(21) NULL DEFAULT NULL,
bounces BIGINT(21) NULL DEFAULT NULL,
time_sent DATETIME NOT NULL,
INDEX campaign_id (campaign_id)
);
#---------------Additional Filters------------------------
if p_filters <> '' then
if p_aff <> '' then
set v_aff = concat(" AND d.affiliate_id=",p_aff);
end if;
if p_tmp <> '' then
set v_tmp = concat(" AND d.template_id=",p_tmp);
end if;
if p_usr <> '' then
set v_usr = concat(" AND d.user_id=",p_usr);
end if;
end if;
#----------------Date boundaries---------------------------
if frm_date <> '' then
set v_fdate = frm_date;
else
set v_fdate = v_frdate;
end if;
if to_date <> '' then
set v_tdate = to_date;
else
set v_tdate = v_todate;
end if;
#------------------role restrictions----------------------
if role_id = '3' then
set v_usr = concat(" AND d.user_id=",p_usr_id);
end if;
if role_id = '2' then
if INSTR(p_filters,'U') = 0 then
set v_usr = '';
end if;
set v_role_id = concat(" AND d.affiliate_id IN (
SELECT affiliate_id FROM user_affiliates
WHERE user_id =",p_usr_id," AND role_id = '2'
)");
else
set v_role_id = "";
end if;
#--------------mailing filter------------------------------
if p_mailing_filter <> '' then
set v_mailing_filter = concat(" AND d.campaign_name LIKE '%",p_mailing_filter,"%'");
end if;
#--------------template filter------------------------------
if p_template_filter <> '' then
set v_template_filter = concat(" AND d.template_name LIKE '%",p_template_filter,"%'");
end if;
#--------------limit----------------------------------------
if p_limit <> '' then
set v_limit = concat(" limit ",p_limit,", 20");
end if;
#--------------sort by---------------------------------------
if sort <> '' then
set v_sort = concat(' order by ',sort);
end if;
#---------------Data insertion in temporary tables------------
insert into camp_users select `c`.`campaign_id` AS `campaign_id`,`c`.`mailing_status` AS `mailing_status`,
`a`.`affiliate_name` AS `affiliate_name`,
`c`.`campaign_name` AS `campaign_name`,
`c`.`template_id` AS `template_id`,`t`.`template_name` AS `template_name`,
`c`.`time_sent` AS `time_sent`,`c`.`affiliate_id` AS `affiliate_id`,`c`.`recipients_count` AS `recipients_count`,
(select count(suppressed_id) from suppressed
where campaign_id=c.campaign_id) suppressed,
`u`.`user_id` AS `user_id`,
`u`.`user_name` AS `user_name` from ((
(`campaigns` `c` join `users` `u` on(
(`c`.`user_id` = `u`.`user_id`)))
join `affiliates` `a` on(
(`c`.`affiliate_id` = `a`.`affiliate_id`))
) join `templates` `t` on((`c`.`template_id` = `t`.`template_id`)))
where (`c`.`mailing_status` = 'a') and date(c.time_sent) between v_fdate and v_tdate;
insert into issue_events select i.campaign_id,i.issue_id,i.issue_id_pk,
(select count(issue_id) from events where issue_id=e.issue_id and event_type=50) unsubscribes,
(select count(issue_id) from events where issue_id=e.issue_id and event_type=10) opens,
COUNT(e.event_id) sends,(select count(issue_id) from events
where issue_id=e.issue_id and event_type=2 and resultcode='R') received,
(select count(issue_id) from events where issue_id=e.issue_id and event_type=2 and
(resultcode='S' or resultcode='T')) bounces,
i.time_sent from events e,issues i where i.issue_id_pk=e.issue_id and e.event_type = 2 and
date(i.time_sent) between v_fdate and v_tdate
group by e.issue_id;
#---------------Dynamic Query----------------------------------
SET v_all_filters = concat(v_usr,v_tmp,v_aff,v_role_id,v_mailing_filter,v_template_filter);
SET v_all_filters = substr(v_all_filters ,instr(v_all_filters,"and")+3);
if v_all_filters <> "" then
SET v_all_filters = concat(" where ",v_all_filters);
end if;
if page = 'L' then
SET @s1 = CONCAT(
"SELECT d.campaign_id, `d`.`affiliate_name`, `d`.`campaign_name`, `d`.`template_id`, `d`.`template_name`,
`d`.`time_sent`, `d`.`affiliate_id`, `i`.`issue_id`,
if(`i`.`sends` IS NULL,`d`.`recipients_count`,`i`.`sends`) recipients_count,IFNULL(`i`.`sends`,0) sends,
IFNULL(`i`.`bounces`,'0') bounces, `d`.`suppressed` , `i`.`received` ,
`d`.`user_id`, `d`.`user_name` from camp_users d
LEFT JOIN `issue_events` i ON `d`.`campaign_id`=`i`.`campaign_id` ",v_all_filters," GROUP BY `d`.`campaign_id`",v_sort,v_limit
);
elseif page = 'T' then # For Template Listing Report
SET @s1 = CONCAT(
"SELECT d.campaign_id, `d`.`affiliate_name`, `d`.`campaign_name`, `d`.`template_id`, `d`.`template_name`,
`d`.`affiliate_id`, `i`.`issue_id`,`d`.`time_sent`,
if(`i`.`sends` IS NULL,`d`.`recipients_count`,`i`.`sends`) recipients_count,IFNULL(`i`.`sends`,0) sends,
IFNULL(`i`.`bounces`,'0') bounces, i.opens, `d`.`suppressed` ,
IFNULL(`i`.`unsubscribes`,'0') unsubscribes, `i`.`received` ,
`d`.`user_id`, `d`.`user_name` from camp_users d LEFT JOIN `issue_events` i ON `d`.`campaign_id`=`i`.`campaign_id` ",
v_all_filters," GROUP BY `d`.`campaign_id`",v_sort,v_limit
);
elseif page = 'F' then # For Franchise Listing Report
SET @s1 = CONCAT(
"SELECT d.campaign_id, `d`.`campaign_name`, `d`.`template_id`, `d`.`template_name`,
`d`.`time_sent`, `i`.`issue_id`,
if(`i`.`sends` IS NULL,`d`.`recipients_count`,`i`.`sends`) recipients_count,IFNULL(`i`.`sends`,0) sends,
IFNULL(`i`.`bounces`,'0') bounces, i.opens, `d`.`suppressed` , IFNULL(`i`.`unsubscribes`,'0') unsubscribes, `i`.`received` ,
`d`.`user_id`, `d`.`user_name` from camp_users d LEFT JOIN `issue_events` i ON `d`.`campaign_id`=`i`.`campaign_id` ",
v_all_filters," GROUP BY `d`.`campaign_id`",v_sort,v_limit
);
end if;
PREPARE stmt1 FROM @s1;
EXECUTE stmt1;
END
3. Sample Function
CREATE DEFINER=`root`@`localhost` FUNCTION `checking_franchise_criteria`(`p_affiliate_id` int, `p_user_id` INT, `recep_count` int)
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
/*
tcnt - used to check having entry in security settings table
temp_count - used to check any campaign is made before.
cntone - used to get count from security settings table of filter type 'U'
cnttwo - used to get count from security settings table of filter type 'F'
max_hour - No of maximum mails per hour threshold in security settings table
max_day - No of maximum mails per day threshold in security settings table
max_list - No of maximum mails per list threshold in security settings table
max_hour_sent - sum of mails already send per hour
max_day_sent - sum of mails already send per day
new_hour - differences of max_hour,max_hour_sent
new_day - differences of max_day,max_day_sent
rtn_value - holds return value
*/
declare tcnt int default 0;
declare cntone,cnttwo,max_hour,max_day,max_list,max_hour_sent,max_day_sent,
new_hour,new_day,temp_count,rtn_value int default 0;
select count(campaign_id) into temp_count from campaigns where affiliate_id=p_affiliate_id
and 0 = abs(datediff(curdate(),time_sent));
select count(security_id) into tcnt from security_settings where affiliate_id=p_affiliate_id;
if tcnt = 0 then
return 2;
elseif tcnt > 0 then
select count(security_id) into cntone from security_settings where affiliate_id=p_affiliate_id and filter_type='U';
select count(security_id) into cnttwo from security_settings where affiliate_id=p_affiliate_id and filter_type='F';
if cntone>0 then
select max_sends_hour,max_sends_day,max_list_size into max_hour,max_day,max_list
from security_settings where affiliate_id=p_affiliate_id and filter_type='U';
select sum(recipients_count) into max_hour_sent from campaigns where affiliate_id=p_affiliate_id and
user_id=p_user_id and 1 > hour(timediff(time(time_sent),curtime()))
and 0 = abs(datediff(curdate(),time_sent)) and mailing_status='a';
select sum(recipients_count) into max_day_sent from campaigns where affiliate_id=p_affiliate_id
and user_id=p_user_id and 0 = abs(datediff(curdate(),time_sent)) and mailing_status='a';
if isnull(max_hour_sent) then
set max_hour_sent = 0;
end if;
if isnull(max_day_sent) then
set max_day_sent = 0;
end if;
if max_hour > 0 then
set new_hour = (max_hour - max_hour_sent);
if recep_count > new_hour then
return 1;
end if;
end if;
if max_day > 0 then
set new_day = (max_day - max_day_sent);
if recep_count > new_day then
return 1;
end if;
end if;
if max_list > 0 and recep_count > max_list then
return 1;
end if;
if max_hour>0 or max_day>0 or max_list>0 then
set rtn_value = 0;
else
return 1;
end if;
end if;
if cnttwo>0 and rtn_value = 0 then
select max_sends_hour,max_sends_day,max_list_size into max_hour,max_day,
max_list from security_settings where affiliate_id=p_affiliate_id and filter_type='F';
select sum(recipients_count) into max_hour_sent from campaigns
where affiliate_id=p_affiliate_id and 1 > hour(timediff(time(time_sent),curtime()))
and 0 = abs(datediff(curdate(),time_sent)) and mailing_status='a';
select sum(recipients_count) into max_day_sent from campaigns
where affiliate_id=p_affiliate_id and 0 = abs(datediff(curdate(),time_sent)) and mailing_status='a';
if isnull(max_hour_sent) then
set max_hour_sent = 0;
end if;
if isnull(max_day_sent) then
set max_day_sent = 0;
end if;
if max_hour > 0 then
set new_hour = (max_hour - max_hour_sent);
if recep_count > new_hour then
return 1;
end if;
end if;
if max_day > 0 then
set new_day = (max_day - max_day_sent);
if recep_count > new_day then
return 1;
end if;
end if;
if max_list > 0 and recep_count > max_list then
return 1;
end if;
if max_hour>0 or max_day>0 or max_list>0 then
return 0;
else
return 1;
end if;
end if;
if temp_count=0 then
return 0;
end if;
end if;
return rtn_value;
END
--------------------------------
Tutorial -11
Procedure for DB search
DROP PROCEDURE IF EXISTS test_v2.sp_testTeam_member_search;
CREATE PROCEDURE test_v2.`sp_testTeam_member_search`(
pmTotalRecords INTEGER,
pmCurPage INTEGER,
pmNoOfRecords INTEGER,
pmLoginUserId INTEGER,
pmUserId TEXT,
pmtestTeamId TEXT,
pmSortflag TINYINT
)
READS SQL DATA
sp_testTeam_member_search:
BEGIN
DECLARE vCurPage INTEGER;
DECLARE vCQuery TEXT;
DECLARE vSql TEXT DEFAULT '';
DECLARE vMemberSql TEXT DEFAULT '';
DECLARE vtestTeamSql TEXT DEFAULT '';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO test_error_log(erlg_object_name,erlg_sqlstate,erlg_error_code,erlg_error_desc,erlg_createdate, erlg_status)
VALUES ('SP_testTeam_MEMBER_SEARCH', 'UNKNOWN_SQL_STATE','-99', 'UNEXPECTED_SQL_EXCEPTION',CURRENT_TIMESTAMP,1);
SELECT -99 Result,NULL MemberId,NULL Member_Fname,NULL Member_Lname,NULL testTeam_Id,NULL testTeam_Description,NULL Type;
END;
/******************* Input Parameters ***************************
Input:
pmTotalRecords - Total Records
pmCurPage - Current Page
pmNoOfRecords - No of Records to List
pmLoginUserId - Logged In UserId
pmUserId - Multiple UserId with comma separator
pmtestTeamId - Multiple testTeamId with comma separator
pmSortflag - Sort Flag(1-testTeam Name (A-Z) 2- testTeam Name (Z-A) 3- Sport (A-Z) 4-Sport (Z-A) 5-City/Town (A-Z) 6-City/Town (Z-A) 7-testTeam Status (Active First))
Output:
Result,
MemberId,
Member_Fname,
Member_Lname,
ZipCode,
City,
testTeam_Id,
testTeam_Logo,
testTeam_Description,
testTeam_Status,
testTeam_amazon_status,
Sport_Name,
testTeam URL
Player_Flag - [0- Not a Player,1- Player,3- Invited]
Friend_Flag - [0- Not a Friend,1- Friend,3- Awaiting for confirmation]
Type - [1- Member,2- testTeam]
Version 1.0 ****************************************************************************/
IF(pmCurPage<0) OR (pmCurPage IS NULL) OR (pmNoOfRecords<=0) OR (pmNoOfRecords IS NULL) THEN
INSERT INTO test_error_log(erlg_object_name,erlg_sqlstate,erlg_error_code,erlg_error_desc,erlg_createdate, erlg_status)
VALUES ('SP_testTeam_MEMBER_SEARCH', 'UNKNOWN_SQL_STATE','-98', 'INVALID_INPUT',CURRENT_TIMESTAMP,1);
SELECT -98 Result,NULL MemberId,NULL Member_Fname,NULL Member_Lname,NULL testTeam_Id,NULL testTeam_Description,NULL Player_Flag,NULL Friend_Flag,NULL Type;
LEAVE sp_testTeam_member_search;
END IF;
SET @vRecordCount :=0;
SET @vTotalPages :=0;
SET vCurPage :=pmCurPage;
IF(vCurPage=1 OR vCurPage=0)THEN
SET vCurPage := 0;
SET @vRowNum := 0;
ELSE
SET @vRowNum := (vCurPage-1)*pmNoOfRecords;
SET vCurPage = (vCurPage*pmNoOfRecords)-pmNoOfRecords;
END IF;
SET vCQuery := " SELECT COUNT(1) INTO @vRecordCount FROM ( ";
SET vSql := " SELECT DISTINCT 1 Result, T.* FROM ( ";
SET vMemberSql :=
CONCAT
(
"
SELECT
@vRecordCount Total_Records,
user.user_id MemberId,
user.user_fname Member_Fname,
user.user_lname Member_Lname,
user.user_zipcode Zipcode,
user.user_city City,
NULL testTeam_Id,
NULL testTeam_Logo,
NULL testTeam_Description,
NULL testTeam_Status,
NULL testTeam_amazon_status,
NULL Sport_Name,
NULL testTeam_URL,
0 Player_Flag,
IF(LENGTH(",pmLoginUserId," )>0,fun_friend_req_status(",pmLoginUserId,",user.user_id),0) Friend_Flag,
1 Type
FROM
test_users user
WHERE
user.user_status=1 AND user.user_type!=2 AND FIND_IN_SET(user_id,'",pmUserId,"')"
);
SET vtestTeamSql :=
CONCAT
(
"
SELECT
@vRecordCount Total_Records,
NULL MemberId,
NULL Member_Fname,
NULL Member_Lname,
testTeam.testTeam_zipcode Zipcode,
CONCAT(testTeam.testTeam_city,', ',stat_abb,' ',testTeam.testTeam_zipcode) City,
testTeam_id testTeam_Id,
testTeam_logo testTeam_Logo,
testTeam_name testTeam_Description,
testTeam.testTeam_status testTeam_Status,
testTeam.testTeam_amazon_status testTeam_Amazon_Status,
sprt.sprt_name Sport_Name,
rutr_url testTeam_URL,
0 Player_Flag,
0 Friend_Flag,
2 Type
FROM
test_testTeam testTeam
INNER JOIN test_router rutr ON rutr.rutr_id=testTeam.testTeam_rutr_id
INNER JOIN test_sport sprt ON sprt.sprt_id=testTeam.testTeam_sprt_id
INNER JOIN test_states stat ON stat.stat_id=testTeam_stat_id
WHERE
testTeam.team_status=1 AND rutr.rutr_status=1 AND stat.stat_status AND sprt_status=1 AND FIND_IN_SET(team_id,'",pmtestTeamId,"')"
);
IF pmUserId IS NOT NULL AND LENGTH(pmUserId)>0 AND (pmtestTeamId IS NULL OR pmtestTeamId='')THEN
SET @vSql := CONCAT(vSql,vMemberSql,')T ');
ELSEIF pmtestTeamId IS NOT NULL AND LENGTH(pmtestTeamId)>0 AND (pmUserId IS NULL OR pmUserId='')THEN
SET @vSql := CONCAT(vSql,vtestTeamSql,')T ');
CASE pmSortflag
WHEN 1 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY testTeam_Description ');
WHEN 2 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY testTeam_Description DESC ');
WHEN 3 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY Sport_Name ');
WHEN 4 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY Sport_Name DESC ');
WHEN 5 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY City ');
WHEN 6 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY City DESC ');
WHEN 7 THEN
SET @vSql := CONCAT(@vSql,' ORDER BY testTeam_Status ');
ELSE
BEGIN
END;
END CASE;
ELSEIF pmtestTeamId IS NOT NULL AND LENGTH(pmtestTeamId)>0 AND pmUserId IS NOT NULL AND LENGTH(pmUserId)>0 THEN
SET @vSql := CONCAT(vSql,vMemberSql,' UNION ALL ',vtestTeamSql,')T ORDER BY Type DESC');
END IF;
SET @vCQuery:= CONCAT(vCQuery,@vSql,')AS T');
IF(pmTotalRecords=0)OR(pmTotalRecords IS NULL) THEN
PREPARE vCountQuery FROM @vCQuery;
EXECUTE vCountQuery;
DEALLOCATE PREPARE vCountQuery;
ELSE
SET @vRecordCount:=pmTotalRecords;
END IF;
IF(@vRecordCount=0)OR((CEIL(@vRecordCount/pmNoOfRecords))<pmCurPage)THEN
SELECT 0 Result,NULL MemberId,NULL Member_Fname,NULL Member_Lname,NULL testTeam_Id,NULL testTeam_Description,NULL Type;
LEAVE sp_testTeam_member_search;
END IF;
SET @vTotalPages := CEIL(@vRecordCount/pmNoOfRecords);
SET @vSql:=CONCAT(@vSql,' LIMIT ',vCurPage,',',pmNoOfRecords);
PREPARE vQuery FROM @vSql;
EXECUTE vQuery;
DEALLOCATE PREPARE vQuery;
END sp_testTeam_member_search;
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
supported function :: fun_friend_req_status
------------------------------------------------------------------------
DROP FUNCTION IF EXISTS test_v2.fun_friend_req_status;
CREATE FUNCTION test_v2.`fun_friend_req_status`(pmUserId INTEGER,pmFriendUserId INTEGER) RETURNS int(11)
READS SQL DATA
DETERMINISTIC
fun_friend_req_status:
BEGIN
DECLARE vStatus INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIN
SET vStatus := 0;
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
INSERT INTO test_error_log(erlg_object_name,erlg_sqlstate,erlg_error_code,erlg_error_desc,erlg_createdate, erlg_status)
VALUES ('FUN_FRIEND_REQ_STATUS', 'UNKNOWN_SQL_STATE','-99', 'UNEXPECTED_SQL_EXCEPTION',CURRENT_TIMESTAMP,1);
RETURN -99;
END;
/******************* Input Parameters ***************************
Author: Rathinsamy
Input:
pmUserId : UserId
pmFriendUserId : Friend UserId
Output:
0- Not a Friend,
1- Friend,
3- Awaiting for Confirmation
Version 1.0
********************************************************************************/
--
SELECT
rlsp_status
INTO
vStatus
FROM
test_relationships
WHERE
rlsp_status IN (1,3)
AND rlsp_rsty_id=6
AND
(
(rlsp_user_id = pmUserId and rlsp_relation_user_id=pmFriendUserId)
OR
(rlsp_user_id = pmFriendUserId and rlsp_relation_user_id=pmUserId)
)
LIMIT 1;
RETURN vStatus;
END fun_friend_req_status;
