Error in stored procedure while migrating mysql syntax to singlestore syntax is not matching...can you please help me to get this syntax. Thanks in advance

Help Procedural SQL
,
1

CREATE PROCEDURE Get_Monitor(in callType varchar(20),in typeName varchar(30),in timeInterval int,in inDate date)
begin
declare msgType varchar(10) default ‘’;
declare timeInt int default 1800;
declare selectInterval varchar(200) default ‘’;
declare orderBy varchar(50) default ‘hour(time)’;
declare msgTypeSuccess varchar(4) default ‘’;
declare countSelection varchar(200) default ‘’;

if(timeInterval != 3600 and timeInterval != 1800 and timeInterval != 900)
then
set timeInt = 3600;
else
set timeInt = timeInterval;
end if;

if (typeName=‘SRISM_INCOMING’)
then
set msgType = ‘14,15’;
set countSelection = ’ sum(case when ORIG_MSG_TYPE in (14,15) then COUNT else 0 end) tot,sum(case when ORIG_MSG_TYPE=15 then COUNT else 0 end) succ ';

elseif (typeName=‘SRISM_OUTGOING’)
then
set msgType = ‘15’;
set countSelection = ’ sum(case when ORIG_MSG_TYPE=15 then COUNT else 0 end) tot,sum(case when ERROR_CODE=255 then COUNT else 0 end) succ ';

elseif (typeName=‘FWDSM_INCOMING’)
then
set msgType = ‘16,17’;
set countSelection = ’ sum(case when ORIG_MSG_TYPE in (16,17) then COUNT else 0 end) tot,sum(case when ORIG_MSG_TYPE=17 then COUNT else 0 end) succ ';

elseif (typeName=‘FWDSM_OUTGOING’)
then
set msgType = ‘17’;
set countSelection = ’ sum(case when ORIG_MSG_TYPE=17 then COUNT else 0 end) tot,sum(case when ERROR_CODE=255 then COUNT else 0 end) succ ';
end if;

if(timeInt = 3600)
then
set selectInterval = "CONCAT(CAST(hour(time) AS CHAR(2)),’:00’) as selIntr ";
elseif(timeInt = 1800) then
set selectInterval = "CONCAT(CAST(hour(TIME) AS CHAR(2)), ‘:’, (CASE WHEN MINUTE(TIME) < 30 THEN ‘00’ ELSE ‘30’ END)) as selIntr ";
elseif(timeInt = 900) then
set selectInterval = "CONCAT(CAST(hour(TIME) AS CHAR(2)), ‘:’, ( CASE WHEN MINUTE(TIME) < 15 THEN ‘00’ when MINUTE(TIME) <30 then ‘15’ when MINUTE(TIME) <45 then ‘30’ when MINUTE(TIME)> 45 then ‘45’ end)) as selIntr ";
end if;

if(callType = ‘RATE’)
then
if(inDate = curdate())
then
set @query1 = concat(“select A.selIntr,A.tot,A.succ ,(A.succ/A.tot)*100 succ_per from (select “, selectInterval ,”,”, countSelection, “from SMS_FIREWALL_SUMMARY where ORIG_MSG_TYPE in (”,msgType,") and (time>(now()-interval 24 hour)) group by selIntr order by hour(time) asc) A");
else
set @query1 = concat(“select A.selIntr,A.tot,A.succ ,(A.succ/A.tot)*100 succ_per from (select “, selectInterval ,”,”, countSelection," from SMS_FIREWALL_SUMMARY where ORIG_MSG_TYPE in (",msgType,") and (date=’",inDate,"’) group by selIntr order by hour(time) asc) A");
end if;
prepare stmt from @query1;
execute stmt;
deallocate prepare stmt;
else
if(inDate = curdate())
then
select ifnull(EC.ERROR_DESC,A.ERROR_CODE) ERROR_CODE,A.count from (select ERROR_CODE,sum(COUNT) count from SMS_FIREWALL_SUMMARY where ORIG_MSG_TYPE=msgType and time > (now() - interval 23 hour) and ERROR_CODE!=255 group by ERROR_CODE order by count desc) A left join ERROR_DETAILS EC on EC.ERROR_CODE=A.ERROR_CODE;
else
select ifnull(EC.ERROR_DESC,A.ERROR_CODE) ERROR_CODE,A.count from (select ERROR_CODE,sum(COUNT) count from SMS_FIREWALL_SUMMARY where ORIG_MSG_TYPE=msgType and date=inDate and ERROR_CODE!=255 group by ERROR_CODE order by count desc) A left join ERROR_DETAILS EC on EC.ERROR_CODE=A.ERROR_CODE;
end if;
end if;
END;

2

Hi Nani! :wave: Welcome to the community forums!

Are you running on the managed or self-hosted service and what version number, please?

3

What error message do you get? I already see typo in your code near succ ';

I would also look at using CASE…WHEN… THEN instead of if.

4

Server version: 5.7.32 MemSQL source distribution

5

The result and values of those variables are not getting entered into the query. I do not understand how to define these variables in a final query:(

6

The result and values of those variables are not getting entered into the query. I do not understand how to define these variables in a final query:(

Thanks in advance…

7

I would follow the definition of create Procedure here.

When you define your stored procedure, you first declare your variables (you only need one Declare command, Then you begin your procedure.

Feel free to use Intercom on our cloud service for getting help but I would first work to make sure the syntax of a stored procedure is followed.