Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert / Update

Author  Topic 

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-05 : 19:22:26
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

38200 Posts

Posted - 2007-02-05 : 19:27:18
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 - 2007-02-05 : 19:30:59
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

22859 Posts

Posted - 2007-02-05 : 23:24:34
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

38200 Posts

Posted - 2007-02-06 : 11:07:41
It's all just a preference thing. There is not just one answer.

Tara Kizer
Go to Top of Page
   

- Advertisement -