Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Unanswered: Array in a STOC??

    Hi all,

    My Issue is the next thing:
    I have a table filled with contracts
    I have Stoc that checks if contract has expired
    If I start the Stoc I must give it the contractnumber with it like:
    SP_expired 8

    This works fine but now I want that the Stoc checks all the contracts by just starting the Stoc.

    I don't really know how to do this, I have thinking about using an array.
    But don't have an idea where to start....

    Thnx in advance
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  2. #2
    Join Date
    Jan 2004
    Posts
    49

  3. #3
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    Originally posted by buser
    Arrays and Lists in SQL Server
    I read the article but I didn't get any further, but thnx alot anyway.
    What I understand now is that I can use CURSORS and i'm understanding this much better.
    If anyone knows if cursors have any disatvantages please let me know...


    thnx in advance
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Cursors have a tendency to be really slow as I belive the contents of the cursor are written to the temp-database when you open it and read it from there. There are situations when you just can't get by without them, but it is generally recomended to try and stay away from them as much as you can...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    Originally posted by Frettmaestro
    Cursors have a tendency to be really slow as I belive the contents of the cursor are written to the temp-database when you open it and read it from there. There are situations when you just can't get by without them, but it is generally recomended to try and stay away from them as much as you can...
    Frettmaestro,

    I want to use my sproc to check if my contracts aren't expired, I shall call this sproc once a day with a trigger. The table will be filled with approximately 300 contracts.
    In my opinion this isn't much, but if you think that this will make my system insecure or much slower please let me know.

    thnx in advance
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  6. #6
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Are you sure you need a cursor? What happens if a contract has/hasn't expired? If you have a datetime-field with a value for when the contract expires it would be really easy to check but it all depends on what you need to do if/if not it has expired.

    SELECT COUNT(*) FROM contracts WHERE StopDate >= GETDATE()
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  7. #7
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    Originally posted by Frettmaestro
    Are you sure you need a cursor? What happens if a contract has/hasn't expired? If you have a datetime-field with a value for when the contract expires it would be really easy to check but it all depends on what you need to do if/if not it has expired.

    SELECT COUNT(*) FROM contracts WHERE StopDate >= GETDATE()
    I have got an expirationdate, and I could do it like you suggested to check which contracts have expired.
    But in the table I have a for example "remind_expired" here I set lets say 6. The 6 is for months.
    my stoc converts the difference between getdat() and expirationdate, if the difference is bigger than the value in "remind_expired" 6, than do nothing else send an email to the administrator of that contract and let him know that the contract needs some kind of special attention.

    I don't think that a simple select statement could help me out.
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  8. #8
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    In that case using a cursor would not be a problem at all. If this only runs once a day then there is nothing to worry about but you should try to stay away from them in the high-volume sp's...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  9. #9
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    Originally posted by Frettmaestro
    In that case using a cursor would not be a problem at all. If this only runs once a day then there is nothing to worry about but you should try to stay away from them in the high-volume sp's...

    Thnx for the help Frettmaestro
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

Posting Permissions

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