Professional Web Applications Themes

is there anything wrong w/ this proc? - Microsoft SQL / MS SQL Server

all i'm doing is creating a procedure, that will pass another parameter to a bigger procedure. it seems that it finds the value of v_org_id only once, and it inserts all the 864 rows that it should. but after, it does this, the transaction keeps running - with no additional rows being added. thanks for any help. CREATE procedure nmetric.nmsched_pop_org_avail_all as declare v_org_id varchar(20) declare getorgid cursor for select DISTINCT org_id from nmsched_org_dim open getorgid fetch next from getorgid into v_org_id begin while (fetch_status != -1) begin if (fetch_status != -2) execute nmsched_pop_org_avail v_org_id end fetch next from getorgid into ...

  1. #1

    Default is there anything wrong w/ this proc?

    all i'm doing is creating a procedure, that will pass another parameter to a
    bigger procedure. it seems that it finds the value of v_org_id only once,
    and it inserts all the 864 rows that it should. but after, it does this,
    the transaction keeps running - with no additional rows being added. thanks
    for any help.


    CREATE procedure nmetric.nmsched_pop_org_avail_all
    as
    declare v_org_id varchar(20)
    declare getorgid cursor for
    select DISTINCT org_id
    from nmsched_org_dim
    open getorgid
    fetch next from getorgid into v_org_id
    begin
    while (fetch_status != -1)
    begin
    if (fetch_status != -2)
    execute nmsched_pop_org_avail v_org_id
    end
    fetch next from getorgid into v_org_id
    end
    close getorgid
    deallocate getorgid


    alex Guest

  2. #2

    Default Re: is there anything wrong w/ this proc?

    Alex,

    The cursor only fetches the first value.
    Try this instead:

    CREATE procedure nmetric.nmsched_pop_org_avail_all
    as
    declare v_org_id varchar(20)
    declare getorgid cursor for
    select DISTINCT org_id
    from nmsched_org_dim
    open getorgid
    fetch next from getorgid into v_org_id
    while (fetch_status != -1)
    begin
    if (fetch_status != -2) execute nmsched_pop_org_avail v_org_id
    fetch next from getorgid into v_org_id
    end
    close getorgid
    deallocate getorgid


    "alex ivascu" <com> wrote in message
    news:%phx.gbl... 

    once, 
    thanks 


    Stefan Guest

  3. #3

    Default Re: is there anything wrong w/ this proc?

    You're stuck in a loop if Fetchstatus = -2. Change you while to while
    Fetchstatus =0.
    Also decalre your cursor as CURSOR LOCAL FAST_FORWARD FOR, this will improve
    performance. Lastly check if you actually need a cursor. Can't you include
    the SQL in nmsched_pop_org_avail with your select statement?

    "alex ivascu" <com> wrote in message
    news:%phx.gbl... 

    once, 
    thanks 


    Jacco Guest

  4. #4

    Default Re: is there anything wrong w/ this proc?

    thanks for the advice, Jacco. I added that piece of code to the main proc,
    and the cursor perf tip was well suited. Any suggestions on delete this
    table quickly? (besides delete table blah?)

    thanks again.

    alex ivascu


    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    improve [/ref]
    to 
    > once, [/ref]
    this, 
    > thanks 
    >
    >[/ref]


    alex Guest

  5. #5

    Default Re: is there anything wrong w/ this proc?

    Thanks, Stefan for catching that!!! I appreciate it.

    Alex Ivascu


    "Stefan" <net> wrote in message
    news:#phx.gbl... [/ref]
    to 
    > once, [/ref]
    this, 
    > thanks 
    >
    >[/ref]


    alex Guest

  6. #6

    Default Re: is there anything wrong w/ this proc?

    >> creating a procedure, that will pass another parameter to a bigger
    procedure. it seems that it finds the value of v_org_id only once...
    <<

    Why are you writing procedural code instead of just putting this query
    in an "... IN (SELECT org_id FROM nmsched_org_dim)" predicate inside the
    body of the nmsched_pop_org_avail() procedure? This looks like a Cobol
    program ...

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  7. #7

    Default Re: is there anything wrong w/ this proc?

    You can't really use TRUNCATE in production code, except data imports maybe.
    Only members of the sysadmin, ddladmin and dbowner roles have permission to
    use it and this permission is not transerable to other users. TRUNCATE also
    doesn't fire delete triggers on the table that is truncated, and you can't
    truncate a table that is referenced by Foreign Keys.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "alex ivascu" <com> wrote in message
    news:phx.gbl... 
    ol' 
    > proc, [/ref][/ref]
    while 
    > > improve [/ref]
    > include [/ref]
    > parameter [/ref][/ref]
    only 
    > > this, 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  2. Proc::ProcessTable
    By Jens Puruckherr in forum PERL Modules
    Replies: 2
    Last Post: August 4th, 11:25 AM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. variable used for proc name
    By John David Adamski in forum Informix
    Replies: 1
    Last Post: September 15th, 05:06 PM
  5. How to 'return' from a Proc?
    By Kero van Gelder in forum Ruby
    Replies: 1
    Last Post: July 7th, 05:08 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139