Stored Procedures and Commiting Data

I have a Stored procedure on a SQL 2005 server.

It inserts a new record into t_people and correctly retrieves the id
using SCOPE_IDENTITY.

It then calls another SP that takes the ID of the new record in
t_people as a parameter.

When the second SP queries t_people to find the data for the person,
it can't see the row and returns EOF.

Is there something I need to do in the first SP to commit the new data
before other SP's can see it?

[Erland Sommarskog]

<option value="13315">Jackson, Duane | | Duane Jackson</option>
(du***@kashflow.co.uk) writes:

I have a Stored procedure on a SQL 2005 server.

It inserts a new record into t_people and correctly retrieves the id
using SCOPE_IDENTITY.

It then calls another SP that takes the ID of the new record in
t_people as a parameter.

When the second SP queries t_people to find the data for the person,
it can't see the row and returns EOF.

Is there something I need to do in the first SP to commit the new data
before other SP's can see it?

No.

I would suggest that you post your code, so that we can getter better
grip of what you are doing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Kevin Zhong]

On Jun 9, 11:05*pm, "<option value=\"13315\">Jackson, Duane | | Duane
Jackson</option>" <[e-mail]:

I have a Stored procedure on a SQL 2005 server.

It inserts a new record into t_people and correctly retrieves the id
using SCOPE_IDENTITY.

It then calls another SP that takes the ID of the new record in
t_people *as a parameter.

When the second SP queries t_people to find the data for the person,
it can't see the row and returns EOF.

Is there something I need to do in the first SP to commit the new data
before other SP's can see it?

hi,the @@IDENTITY can help your.

when your insert a new record. your can used the @@IDENTITY to get the
id.
code:
INSERT INTO T_TABLE (xxxx) VALUES (xxxx)
SELECT @@IDENTITY