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 . ! .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.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx