Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Standard Store procedure look like


Standard Store procedure look like

Author
Message
pandeharsh
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 337
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
arun.sas
arun.sas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 3493
Hi,

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

ARUN SAS
pandeharsh
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 337
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
arun.sas
arun.sas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 3493
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
pandeharsh
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 337
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
arun.sas
arun.sas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 3493
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
:-)
pandeharsh
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 337
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
arun.sas
arun.sas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 3493
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17529 Visits: 32252
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
Ramesh Saive
Ramesh Saive
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2564 Visits: 2643
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search