pandeharsh (3/31/2009)
Hi,Suppose i write a general proc like this.
Create proc Example
@empid int,
@Mode varchar (1),
@fname varchar (100),
@lname varchar(100)
As
If (@Mode='S'
Begin
Select fname,lname from emp where empid=@empid
End
If (@Mode='I'
Begin
Insert into emp(fname,lname) values(@fname,@lname)
End
If (@Mode='U')
Begin
Update emp set fname=@fname,lname=@lname where empid=@empid
End
GO
This run ok.How should i implement error handler,comit and rolback transaction,like any standard strore procedure.
Also according to you all,what is wrong in this store procedure.
Thanks
It's going to recompile almost every single time it gets called. The procedure name is used to check against cache to see if there is an existing plan and then the plan is verified against the query being run. If the first time through it creates a plan for the SELECT query and the second time through it goes to run the INSERT statement, it's going to cause a recompile. This will pause the execution of the plan and possibly block other sessions while the plan recompiles (although you could get statement level recompiles). If you really wanted to do it this way, better to create a procedure for each independent action and then call those procedures from this wrapper procedure. That way each procedure gets it's own execution plan that doesn't need to be recompiled over & over.
It works for really simple tables, but it's going to get messy and hard to maintain when you get to larger tables. Also, this is predicated on single row inserts, updates & deletes. What happens when you have to start dealing with sets? I wouldn't generally do things like this.
But yes, I would put transactions and error handling in place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning