MS SQL server programming

Posted on 2012-09-20
Last Modified: 2012-09-21
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  and select the channelid where dbo.smsin.message =
if it's equal insert into table dbo.subscription the msisdn and the channelid
to subscripe  the user for this service.
no channelid try to insert into another table for reporting...

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

SELECT shortcode,message,msisdn
FROM dbo.CGI_SMS_dafif;

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


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

CLOSE sms_in_cur;
DEALLOCATE sms_in_cur;
Question by:afifosh
    LVL 39

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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
    LVL 1

    Author Comment

    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
    LVL 1

    Author Comment

    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)

    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 :)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now