Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
/*****************
---------
-- REPORT --
--------
******************/
--
-- if the msg is nothing text back to recepient and say...try
again--------------
if @campaign_message = ''
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'Oops, we did not understand ur reply. Probably due
2 spelling error
or u took 2 long to reply. Pls check spelling and resend to' +
@sms_server_number + '. U can reply STOP 2 end.')
END
----------------------------------------------------------------------
---------------
-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'You have been opted out of this campaign. Thank you for
your time.')
Update grp_campaign set stop = 'STOP' where to_number = @to_number and
sms_server_num = @sms_server_number
END
-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN
-- 1. get the old message into a var
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
-- 2. get convo id...
declare @convo_id nvarchar(max)
set @convo_id = (SELECT convo_id from grp_campaign WHERE to_number =
@to_number)
-- 3. then tack on the newest response + NEW LINE SPACE!!!
declare @grp_tc_id nvarchar(max)
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
declare @new_part nvarchar(max)
set @new_part = @campaign_message
--set @new_part = @old_grp_msg + '%0a' + UPPER(@convo_id) + ' :' +
--'%0a' + @new_part + '%0a'
set @new_part = @old_grp_msg + ' <<< ' + UPPER(@convo_id) + ' >>> ' + ' :' +
' ' + @new_part + ' '
-- 4. get the last 500 chars into a new var update grp_campaign set
rolling_convo_msg = RIGHT(@new_part,250) where tc_id = @grp_tc_id
-- 5. send update msg to all participats insert into grp_message_queue
(to_number,rolling_convo_msg,sms_server_num)
SELECT to_number, rolling_convo_msg,sms_server_num from grp_campaign
where tc_id = @grp_tc_id
END
/*****************
---------
-- REPORT --
--------
******************/
--
-- if the msg is nothing text back to recepient and say...try
again--------------
if @campaign_message = ''
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'Oops, we did not understand ur reply. Probably due
2 spelling error
or u took 2 long to reply. Pls check spelling and resend to' +
@sms_server_number + '. U can reply STOP 2 end.')
END
----------------------------------------------------------------------
---------------
-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'You have been opted out of this campaign. Thank you for
your time.')
Update grp_campaign set stop = 'STOP' where to_number = @to_number and
sms_server_num = @sms_server_number
END
-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN
-- 1. get the old message into a var
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
-- 2. get convo id...
declare @convo_id nvarchar(max)
set @convo_id = (SELECT convo_id from grp_campaign WHERE to_number =
@to_number)
-- 3. then tack on the newest response + NEW LINE SPACE!!!
declare @grp_tc_id nvarchar(max)
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
declare @new_part nvarchar(max)
set @new_part = @campaign_message
--set @new_part = @old_grp_msg + '%0a' + UPPER(@convo_id) + ' :' +
--'%0a' + @new_part + '%0a'
set @new_part = @old_grp_msg + ' <<< ' + UPPER(@convo_id) + ' >>> ' + ' :' +
' ' + @new_part + ' '
-- 4. get the last 500 chars into a new var
update grp_campaign set
rolling_convo_msg = RIGHT(@new_part,250) where tc_id = @grp_tc_id
-- 5. send update msg to all participats
insert into grp_message_queue (to_number,rolling_convo_msg,sms_server_num)
SELECT to_number, rolling_convo_msg,sms_server_num from grp_campaign
where tc_id = @grp_tc_id
END
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
can raise the error mentioned if there are 2 or more rows for the selected @sms_server_num value. update c
set rolling_convo_msg = RIGHT(@new_part,250)
from grp_campaign c
where to_number = @to_number
and sms_server_num = @sms_server_number
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.