Results 1 to 12 of 12

Thread: SP Code

  1. #1
    Join Date
    Feb 2004
    Posts
    17

    Unanswered: stored proc script

    Hi,

    The script below is just the beginning of something a bit more complicated, which I'm not sure how I'll explain so depending on the amount of posts I'll explain further.

    For the time being can anybody see if there's anything wrong with how I've scripted the SP below particulary the loop (Check thread - Stored Procedure Programming)?

    Thanks for your input in advance.



    CREATE PROCEDURE newimr_sp_duplicate_report
    (
    @countryid as INT,
    @marketsectorid as INT, -- marketsector to transfer from
    @specialreportid as INT
    )
    AS

    Declare
    @other_marketsectorid as INT

    if (@marketsectorid = 1)
    set @other_marketsectorid = 2
    else
    set @other_marketsectorid = 1

    declare topicids cursor for
    select id, description from tbl_topics where specialreportid = 7 and marketsectorid = @marketsectorid
    open topicids
    while 1 = 1
    begin
    declare
    @other_topicid as int,
    @description as varchar(255)
    fetch next from topicids into @other_topicid, @description
    if @@fetch_status <> 0
    break

    select @description

    end

    close topicids
    deallocate topicids

    GO
    Last edited by cstevio; 09-26-05 at 08:41.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. The basic problem with your cursor loop is that you are using a cursor loop. Cursors are to be avoided in ineffecient SQL programming, in favor of set-based operations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    17
    Not sure I understand what you mean by set-based operations?

    Could you explain a little further?

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cstevio, how many posts do you need before you explain further?

    what's wrong with having the stored proc return a result set?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First of all, all it looks like is that you want to get the last description from a result set.

    Only problem is that your cursor does not have an order by, so, it you run this over and over, it is very likely that you could get different results every time you run it.

    Like I saud before, please tell us what you're trying to accomplish. Please don't not try and lead us down a technical solution path.

    Follow the suggestions in this link

    http://weblogs.sqlteam.com/brettk/ar...5/25/5276.aspx

    And btw, assuming you wan the order by [id], this is the exact same thing as your cursor

    Code:
    SELECT TOP 1 [description] 
      FROM tbl_topics 
     WHERE specialreportid = 7 
       AND marketsectorid = @marketsectorid
    ORDER BY [id] DESC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    17
    Okay, here's my attempt to get across what I want to achieve.

    I have a table called reports with the following fields:

    id, description, topicid, countryid

    I want to duplicate data for a particular country.

    topicid is the value that will be different once I duplicate the data as the data in the topic table is as follows:

    ------------------------------------------
    id description marketsectorid
    ------------------------------------------
    8 General Info 1
    9 General Info 2

    I know this is duplication of data and it is not a good idea at all, but this is part of a bigger system so without having to change too much I've I had to duplicate data.

    Basically, I'm looping thru the reports table for a particular country and getting the topicid for marketsector 1 and re-inserting as marketsector 2 data.

    This is the result I want to achieve

    ----------------------------------------------
    id description topicid countryid
    ----------------------------------------------
    1 blah blah blah 8 7
    2 blah blah blah 9 7

    I have currently managed to do this with some success in an ASP page and using ASP to compare and loop thru a result set but it's producing inconsisten results so I figure I try with stored procs.

    Not sure if the above is clear at all.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you open the link that I sent you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cstevio
    Okay, here's my attempt to get across what I want to achieve.
    it's not at all clear which table you're trying to insert the duplicates into

    the set-based solution will involve INSERT/SELECT synyax, like this --
    Code:
    insert
      into reports 
         ( id 
         , description 
         , topicid 
         , countryid )
    select r.id
         , t.description
         , r.topicid
         , 7
      from reports as r
    inner
      join topics as t
        on r.topicid = t.id
       and t.marketsectorid = 1
     where r.countryid = 7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Posts
    17
    yes, I understand I'm supposed to post my query with the tables in question and some sample data. But where on that page? I clicked on the SQLTeam link and then I'm not sure where to go... don't know if I'm really stoopid or what...

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No post it here...post the DDL, the sample data inserts, and the expected results.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brett, perhaps what you could do is write up the "here's how to ask a question" stuff as a new thread here, and i'll make it a "sticky" which means it will forever stay at the top of the forum list (for example, see mysql forum)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Posts
    17
    Brett, I'll prepare something for you tomorrow... I'm pretty snowed under at the moment...

    Rudy, thanks for the set-operation example, it gave me a great idea which I'm still working on...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •