March 31, 2009 at 12:25 am
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
[font="Verdana"]Regards
Kumar Harsh[/font]
March 31, 2009 at 2:18 am
Hi,
Show you emp table structure
Because during insert, your not call emp colums
ARUN SAS
March 31, 2009 at 2:34 am
Hi,
You can assume anything.Though structure of my table is
CREATE TABLE [emp] (
[empid] [int] IDENTITY (1, 1) NOT NULL ,
[Fname] [varchar] (100) NOT NULL ,
[Lname] [varchar] (100) NOT NULL
) ON [PRIMARY]
GO
[font="Verdana"]Regards
Kumar Harsh[/font]
March 31, 2009 at 2:40 am
Hi,
In your table shows all col are not null,
Then in the sp line
If (@Mode='I'
Begin
Insert into emp(fname,lname) values(@fname,@lname)
End
returns with error
AND what error you need to return,
Because it’s automatically return by not null column
ARUN SAS
March 31, 2009 at 2:53 am
Hi,
It should not accept null values.Now it does and do not return any value and record get save.
It should not accept null values at all.
[font="Verdana"]Regards
Kumar Harsh[/font]
March 31, 2009 at 3:01 am
Hi,
Because the table column having IDENTITY (1, 1)
You should not pass the value to this column, so that its wound raises the error
ARUN SAS
🙂
March 31, 2009 at 3:07 am
Hi,
Listen, I am not passing value to empid which is identity(1,1).
Fname and Lname are not null,but when null value pass,it accept it and do not throw any error.
Try to understand my problem.
[font="Verdana"]Regards
Kumar Harsh[/font]
March 31, 2009 at 3:24 am
HI,
try this then exec the sp with null
CREATE proc Example
(
@empid int,
@Mode varchar (1),
@fname varchar (100)= null,
@lname varchar(100)=null
)
As
begin
-- CREATE TABLE [emp] (
-- [empid] [int] IDENTITY (1, 1) NOT NULL ,
-- [Fname] [varchar] (100) NOT NULL ,
-- [Lname] [varchar] (100) NOT NULL
-- ) ON [PRIMARY]
-- GO
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
end
go
March 31, 2009 at 4:45 am
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
March 31, 2009 at 4:56 am
Personally, I don't like writing everything in a single procedure. Instead, I write procedures according to their need and follow these standard rules for any basic table.
Take for an example of mstEmployees table, for this table I will create procedures for GET (usp_mst_GetEmployeeByID), GET ALL (usp_mst_GetEmployees) & SAVE (usp_mst_SaveEmployee) employees.
And regarding error & transaction handling, you really don't need that in these type of procedures, as the GET/GET ALL methods are just plain SELECTs and SAVE method is either INSERT or UPDATE (only 1 DML statement).
--Ramesh
March 31, 2009 at 4:56 am
Hi,
Thanks a lot for advice.
Like you mention,that you would add transaction and error handler,how will you go about it ?I just need a proper structure of it.
Thanks
[font="Verdana"]Regards
Kumar Harsh[/font]
March 31, 2009 at 5:13 am
You don't need error handling or transactions for the SELECT part of the query. There's nothing going on there that requires it.
As to the rest, here's an article I wrote on error handling[/url]. Just refer to the 2005 code, not the 2000 code. If you're going to insist on having this entire thing in one proc (not a good idea), then I'd put the TRY/CATCH around the whole thing and the same with the transaction. There's a section on using transactions in the same code, but you can do a search to find better examples.
"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
March 31, 2009 at 10:58 am
Hi
You can use BEGIN TRANSACTION things like this but this may not help you in all the ways. Especially when you have batch updates then you want to put these in single transaction. So when you are doing any DML operations, it completely depends on the requirements.
Thanks -- Vijaya Kadiyala
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply