Stored Procedures and Commiting Data

[Guest]

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]

Re: Stored Procedures and Commiting Data

<option value="13315">J ackson, Duane | | Duane Jackson</option>
(duane@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Kevin Zhong]

Re: Stored Procedures and Commiting Data

On Jun 9, 11:05 pm, "<option value=\"13315\" >Jackson, Duane | | Duane
Jackson</option>" <du...@kashflow .co.ukwrote:

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