Insertion problem using stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insertion problem using stored procedure

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-07-26 : 18:21:52

I have to insert data into the corressponding tables using stored procedure.Sequence is Table1,2,3 and 4.So four stored procedures are being used.Now the problem is when the insert in done the first 3 insert statements happens but the 4rth insertion is not happening.Its getting locked So 1st,2nd and 3rd insertion happens and as soon as it moves to the 4rth insertion the table is getting locked adn generates an error.what could be the reason

Table 1:
Fields : emp_id ,name,age
primaryKey : emp_id

Table2:
Fields : Dept_id,Dept_name,Dept_block
primaryKey : Dept_id

Table3:
Fields : Inc_id,emp_id,mark1.mark2,total
ForiegnKey : emp_id

Table4:
Fields : emp_id,dept_id,referrence_prof
ForeignKey :emp_id,dept_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-26 : 18:33:44

Show us your code and the error.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-07-26 : 18:54:17

There is no error as such but the table is getting locked meaning the 4rth insertion gets locked.I don't know whether the 4rth stored procedure calls the id from the second table before it gets updated.But according to the sequence it should work.No error message come .I checked the status using sp_who. its just an insert into table statement..No special tricks used

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-26 : 19:01:19

We'll need to see the code. All 4 stored procedures. How are you calling them? Does sp1 call sp2 which calls sp3 which calls sp4, or just:

EXEC sp1
EXEC sp2
EXEC sp3
EXEC sp4

???

Why do you need four stored procedures? Seems to me that this would be in one stored procedure with a transaction wrapped around it.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-07-26 : 19:06:23

The application needs separate stored procedure like
EXEC sp1
EXEC sp2
EXEC sp3
EXEC sp4
and is executed in this sequence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-26 : 19:16:29

We'll need to see the stored procedure code.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-07-26 : 23:07:53

Here emp_details table has an Incremental primary key emp_id
Dept_details table has an incremental primary key dept_id
point_details table has an Incremental key Point_id and a forign key emp_id
benefit_details table has 2 forign keys emp_id and dept_id.

the first 3 stored procedures inserts and when it comes to the 4rth stored procedure its getting locked..its not inserting but no error is being showing as such.

-----------------------------------------------------------------------------------------------------------------------------
CREATE Procedure dbo.usp_emp_id(

@Name varchar(50),
@age int,
@statement varchar(255),
@created_by varchar(50),
@created_on datetime,

)

AS

SET NOCOUNT ON

--- Insert Statement
INSERT INTO
emp_details
(
emp_name,
emp_age,
emp_statement,
created_by,
created_on
)
VALUES
(
@Name,
@age,
@statement,
@created_by,
@created_on,
)

---------------------------------------------------------------------------------------------------------------------------------------

CREATE Procedure dbo.usp_Dept_id(

@Dept_name varchar(50),
@good_through_date datetime,
@dept_block varchar(50),
@statement varchar(255),
@created_by varchar(50),
@created_on datetime,

)

AS

SET NOCOUNT ON

--- Insert Statement

INSERT INTO dept_details
(
dept_name,
good_through_date,
dept_block,
statement,
created_by,
created_on
)
VALUES
(
@Dept_name ,
@good_through_date ,
@dept_block ,
@statement,
@created_by,
@created_on
)

----------------------------------------------------------------------------------------------------------------------------------------

CREATE Procedure dbo.usp_emp_point_details(

@emp_id int,
@point int,
@offers varchar(50),
@created_by varchar(50),
@created_on datetime,
)

AS

SET NOCOUNT ON

--- Insert Statement
INSERT INTO point_details
(
emp_id,
point,
offers,
created_by,
created_on
)
VALUES
(
@emp_id ,
@point,
@offers ,
@created_by,
@created_on
)

----------------------------------------------------------------------------------------------------------------------------------------

CREATE Procedure dbo.usp_emp_benefit_details(

@emp_id int,
@Dept_id int,
@benefits varchar(250),
@created_by,
@created_on
)

AS

SET NOCOUNT ON

--- Insert Statement
INSERT INTO benefit_details
(
emp_id,
Dept_id,
benefits,
created_by,
created_on
)
VALUES
(
@emp_id ,
@Dept_id ,
@benefits ,
@created_by,
@created_on
)

----------------------------------------------------------------------------------------------------------------------------------------

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 23:40:36

??????? That's getting locked? Are you kidding me? Have you looked at the parameters that happen when it gets locked? Are two people trying to insert the same record at a time for example? Also, how do you have code written that calls this? Is this all wrapped inside one transaction?

Do you have relationships setup correctly between this table (foreign key on emp_id and dept_id for the benefit_details table for example)?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources