Post reply

can anyone provide me real time scenarios where you build stored procedures, triggers, cursors?

  • March 9, 2015 at 1:16 pm

    #1782688

    Real World....kinda...

    Let's say you run a site similar to this one. You need to update each user's number of points. You could just query the database for each user and have your query count the number of articles written, correct answers to the Question of the Day, and number of posts. You could write that query every time you needed to update the points, but it would get to be a problem. Instead, you would create a stored procedure that would find the users' handle (nickname) and then do the counting. You could set up the stored procedure so it took a parameter of the persons handle or if one wasn't supplied it would run the process for every user (this is thinking ahead that you might need to do this for one person if their count got messed up).

    For triggers, lets say you want to know when there is a new member. You have a table that has everyone's name and handle. You could set up an Insert trigger on it to alert you when a new member is inserted into the table. Or the trigger could handle other work that you want to have happen behind the scenes - maybe it runs the stored procedure to update the users points to 0 and send them an email thanking them for joining.

    Basically, you set up a stored procedure/function anytime you have repeatable code. And you set up a trigger whenever you want one action to cause something else to happen.

    -SQLBill

  • March 9, 2015 at 3:23 pm

    #1782716

    steve000 (3/9/2015)

    Do you have a sample you would be willing to share? (Stored proc working with csv?) I do SSIS CSV imports all the time!! I didn't know i could avoid that entirely ...

    I can't, my SSIS Fu is VERY weak. My understanding is that if you don't use SSIS, you can use CLR to import a file then do further processing with T-SQL, but I don't know CLR. I'm VERY old-school: I liked DTS and have a lot of difficulty with SSIS.

    When I last wrote something like this, I had a job run by SQL Agent that essentially was three steps: OS job to FTP the data down, SSIS to import the file (package created using the SSMS Import Wizard), SQL to process the data and generate the acceptance/rejection report, then externally an Access DB to do the reporting for the system. But that was a previous job and I don't have the code to be able to seriously talk about it, I only remember in general how it worked.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • March 9, 2015 at 4:14 pm

    #1782726

    steve000 (3/9/2015)

    Do you have a sample you would be willing to share? (Stored proc working with csv?) I do SSIS CSV imports all the time!! I didn't know i could avoid that entirely ...

    Lookup BULK INSERT and BCP in "Books Online".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply