Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

MS SQL server programming

i want to create a procedure for mssql
to fetch all row in table dbo sms in !!!!!!  to proccess the content of message
when i select the row  i try to select the field message and
i will go to select from table dbo.channel  and select the channelid where dbo.smsin.message = dbo.channel.subscriptioncode
if it's equal insert into table dbo.subscription the msisdn and the channelid
to subscripe  the user for this service.
else
no channelid try to insert into another table for reporting...

DECLARE @shortcode INT
DECLARE @message varchar(50)
DECLARE @msisdn varchar(50)

DECLARE sms_in_cur CURSOR FOR
SELECT shortcode,message,msisdn
FROM dbo.CGI_SMS_dafif;

OPEN sms_in_cur
FETCH NEXT FROM sms_in_cur INTO @msisdn, @message, @shortcode
WHILE @@FETCH_STATUS = 0
   BEGIN
    /*



*/

 FETCH NEXT FROM sms_in_cur INTO @msisdn, @message, @shortcode

   END
CLOSE sms_in_cur;
DEALLOCATE sms_in_cur;
dbo-sms-in.png
dbo.channel.png
dbo.subscription.png
0
afifosh
Asked:
afifosh
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
No need for a cursor:


-- insert into subscription
insert into subscription
select <fields>
from channel c
--get only the matches
join smsin i on  c.subcriptioncode = i.message


--Get non matches.
insert into <tbl_report>  --what's your reporting table?
select fields
from channel c
left join smsin i on c.Subscriptioncode = i.message
where i.Message is null    -- if there is no match, i.message will be null.
0
 
afifoshAuthor Commented:
i want to tell you... that i have a table whre i have a lot of new msg every couple of second with different shortcode i have make a new table sms in for a specifc shortcode

and this short code is sms in ..
so i have a procedure to fetch all row in the first table and put it in sms in

than i want to proccess all message on sms in after i have copied from the first table to sms in ..the processing is by send sms to subscribed users..

and who i can check if user has already subscribed? if he is already subscribed it insert a row in table like report !! to give me feedback
0
 
afifoshAuthor Commented:
and if the message is not the same of susbrictioncode found in channel i insert in specific table a notifcation to send later.. a sms to him !!

so who i can write it :S
0
 
afifoshAuthor Commented:
i work in nowsms  all msg received from mobile operator will be stored on cgi_sms_received
table , i am new employer in my company i have already found this main trigger kindly find the attachment documents !

the main trigger       SELECT top 20 ID,ReqPort,RecdPort,Message,Msisdn,coding FROM cgi_sms_received

and try to fetch all record ! and see sms sent to specific short code using

            if(ltrim(rtrim(@reqport)) = 752267) begin
                  insert into CGI_SMS_d752267(reqport,recdport,message,msisdn,coding) values  (@reqport,@recdport,@message,@msisdn,@coding)
            end


so for example cgi_sms_d752267 is db sms in

now i want to add new procedure to proccess all send sms for subscribe user to specific service  when matching the content of message !! with suscription code on channel table
than i will insert autmaticly a new record on suscription table and i before suscription i should check if user is already subscribed if he already suscribe to this service i insert in report a notification alert !! !!!

this is my problem each couple of second i recive new sms on this short code table ..
and i want to process it so what is the good solution .??


and i need to make a application to fetch all row to process it ??
and please tell me if i want to put it as new question :)
main-trigger.docx
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now