Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    UAE , AD

    Question Unanswered: Best Practice for this SP Scenario !

    Hello All ..
    This is the scenario I'm having :
    -- I'm a beginner so bear the way I'm putting it ... sorry !

    * I have a database with tables
    - company: CompanyID, CompanyName
    - Person: PersonID, PersonName, CompanyID (fk)
    - Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)

    In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...

    There could be different ways to do it like:
    1) - In the supplier stored procedure I can read the ID (SELECT) and :

    if it exists (I save the existing SupplierID - to 'return' it at the end).
    if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
    2) - Other way is by doing multiple stored procedures,
    . one SP that checks,
    . another SP that do inserts
    . and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)

    3) it could be done (maybe) using Functions in SQL SERVER...

    There should be some reasons why I need to go for one of the methods or another method !
    I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario ..... !!!

    I'll appreciate your help ...
    Thanks in Advance . ! .

  2. #2
    Join Date
    May 2002
    Sql2k recompiles the entire sp if a recompilation is needed. Thus, it's best to split up the sproc into child sprocs. So, your #2 would be the way to go.

    I suggest you read up on this excellent article.

Posting Permissions

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