SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert / Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 02/05/2007 :  19:22:26  Show Profile  Reply with Quote
Hello,

I am creating an Insert and an Update procedure for a table.
Do I really need to create 2 different procedures?

What is the best way to do this?
Or maybe the standard way to do this?

As far as I can see, in a simple table, the difference would be providing the PK (ID) of the record or not ... or maybe I am wrong.

Thanks,
Miguel





tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 02/05/2007 :  19:27:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
You do not need to create two different stored procedures.

I prefer to use different stored procedures for each thing I need to do. Each stored procedure could have various statements in them though.

Tara Kizer
Go to Top of Page

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 02/05/2007 :  19:30:59  Show Profile  Reply with Quote
You mean that you would have a Insert and an Update procedure.
And the decision to use one or the other would be done on the, for example, .NET code.
Even if the SQL code is so similar right?

It is just that I was putting everything together with some IF's and sometimes it gets confusing ...

Thanks,
Miguel
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/05/2007 :  23:24:34  Show Profile  Reply with Quote
We use single a procedure for Insert & Update (what we refer to as "UpSert"!)

If the record does not exist the SProc INSERTs it, otherwise it UPDATEs it.

The SProc has a parameter for whether it should expect that the record exists, or not, but the parameter is optional. When the program knows it sets the parameter accordingly (and the Sproc will raise an error if that condition is not met); if the program doesn't know / care then the Sproc will perform its action according to whether a record already exists, or not.

But that's just the way we chose to do it!

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 02/06/2007 :  11:07:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's all just a preference thing. There is not just one answer.

Tara Kizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000