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.
| Author |
Topic |
|
Blade_316
Starting Member
5 Posts |
 Posted - 2007-05-16 : 13:56:57
|
| What are your opinions on the combination of both the insert and update procedures.IF @Id= 0 BEGIN INSERT INTO tblTemp (Field1) VALUES (@Field1)ENDELSE BEGIN UPDATE tblTemp SET Field1 = @Field1 WHERE Id = @IdENDAnd I know there are multiple ways of doing it... What are the best approaches.1. Using Exists()2. Updating and checking @@ROWCOUNT3. Like writen aboveOr should it be avoided at all costs. I'm currently using SQL Server 2005 SP2 |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2007-05-17 : 00:55:22
|
| Hi,If you are passing @Id as 0 in case of Insert then its better to use the way you have written it write now.Because in both the rest choices you have to make an extra hit for checking the existence. |
 |
|
|
Blade_316
Starting Member
5 Posts |
Posted - 2007-05-17 : 11:45:54
|
| Yeah, passing @Id as 0 is the most efficient way. But my initial concern was that if I combined both into the same query with the IF condition, I might suffer from a recompile each time I switch from an insert to an update. I did some testing, and found out that it won't recompile if my save switches from an insert to an update. So that's great! Profiler shows 2 Query Plans being created the first time it executes, one for insert and one for updateCan anyone tell me in which situation a stored procedure will absolutely get recompiled each and every time. |
|
|
|
|
|
|
Combined Insert and Update into a single procedure