Problem with stored procedure and retrieving inserted identity value

[Susanne Klemm]

Hello!

I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.

My Procedure:

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

My C# code:
SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@NEWID",
System.Data.SqlDbType.Int)).Direction =
System.Data.ParameterDirection.Output;

try
{
SqlDataReader sqlRead = comm.ExecuteReader();
object o = comm.Parameters["@NEWID"].Value;
//...
}
catch ( Exception ex ){ throw ex;}

The object o is alwaya System.DbNull. I also tried to use bigint.

Any hints are welcome

Ciao
Susanne

[Erland Sommarskog]

Susanne Klemm (sk****@gmx.de) writes:

I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.

There is nothing magic around 700000. Overall, everything you have is 32-bit
integer, so there should be no reason for a clash.

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

In this case, it's better to use scope_identity(), which returns the most
recently generated identity value in the current scope. If PRODUCT has a
trigger which inserts data into a table with another IDENTITY column,
@@identity will return the value for that latter table.
--
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

[Suleon]

Erland Sommarskog wrote:

Susanne Klemm (sk****@gmx.de) writes:

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

In this case, it's better to use scope_identity(), which returns the most
recently generated identity value in the current scope. If PRODUCT has a
trigger which inserts data into a table with another IDENTITY column,
@@identity will return the value for that latter table.

I tried scope_identity() but got no changes. But indeed there was a
trigger on the product table. After removing it both variants worked.

Thank you!
Susanne

[rmk]

select @NEWID = @@IDENTITY

should be changed to

SET @NEWID = SCOPE_IDENTITY()
"Susanne Klemm" <sk****@gmx.dewrote in message
news:44*********************@reader.news.celox.de. ..

Hello!

I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.

My Procedure:

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

My C# code:
SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@NEWID",
System.Data.SqlDbType.Int)).Direction =
System.Data.ParameterDirection.Output;

try
{
SqlDataReader sqlRead = comm.ExecuteReader();
object o = comm.Parameters["@NEWID"].Value;
//...
}
catch ( Exception ex ){ throw ex;}

The object o is alwaya System.DbNull. I also tried to use bigint.

Any hints are welcome

Ciao
Susanne