Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Standard Store procedure look like Expand / Collapse
Author
Message
Posted Tuesday, March 31, 2009 12:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 3, 2014 10:22 PM
Points: 97, Visits: 324
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


Regards
Kumar Harsh

Post #686746
Posted Tuesday, March 31, 2009 2:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
Hi,

Show you emp table structure
Because during insert, your not call emp colums

ARUN SAS
Post #686780
Posted Tuesday, March 31, 2009 2:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 3, 2014 10:22 PM
Points: 97, Visits: 324
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


Regards
Kumar Harsh

Post #686787
Posted Tuesday, March 31, 2009 2:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
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
Post #686790
Posted Tuesday, March 31, 2009 2:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 3, 2014 10:22 PM
Points: 97, Visits: 324
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.



Regards
Kumar Harsh

Post #686796
Posted Tuesday, March 31, 2009 3:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
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

Post #686797
Posted Tuesday, March 31, 2009 3:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 3, 2014 10:22 PM
Points: 97, Visits: 324
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.


Regards
Kumar Harsh

Post #686801
Posted Tuesday, March 31, 2009 3:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
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

Post #686809
Posted Tuesday, March 31, 2009 4:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #686850
Posted Tuesday, March 31, 2009 4:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:30 AM
Points: 2,552, Visits: 2,599
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

Post #686860
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse