Standard Store procedure look like

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Standard Store procedure look like

13 posts, Page 1 of 2

1 2 »»

Standard Store procedure look like

Rate Topic

Display Mode

Topic Options

Author

Message

pandeharsh

Posted Tuesday, March 31, 2009 12:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 28, 2012 4:19 AM
Points: 94, Visits: 290

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
Unsure

Post #686746

arun.sas

Posted Tuesday, March 31, 2009 2:18 AM

Ten Centuries

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

Hi,

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

ARUN SAS

Post #686780

pandeharsh

Posted Tuesday, March 31, 2009 2:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 28, 2012 4:19 AM
Points: 94, Visits: 290

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
Unsure

Post #686787

arun.sas

Posted Tuesday, March 31, 2009 2:40 AM

Ten Centuries

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

pandeharsh

Posted Tuesday, March 31, 2009 2:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 28, 2012 4:19 AM
Points: 94, Visits: 290

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
Unsure

Post #686796

arun.sas

Posted Tuesday, March 31, 2009 3:01 AM

Ten Centuries

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

Post #686797

pandeharsh

Posted Tuesday, March 31, 2009 3:07 AM

SSC Journeyman

Group: General Forum Members
Last Login: Sunday, October 28, 2012 4:19 AM
Points: 94, Visits: 290

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
Unsure

Post #686801

arun.sas

Posted Tuesday, March 31, 2009 3:24 AM

Ten Centuries

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

Grant Fritchey

Posted Tuesday, March 31, 2009 4:45 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187

pandeharsh (3/31/2009)

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #686850

Ramesh Saive

Posted Tuesday, March 31, 2009 4:56 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions