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_CREAT ED)values(getda te()+'')
set nocount off
select @NEWID = @@IDENTITY
end

My C# code:
SqlCommand comm = new SqlCommand("Ins ertProduct", sqlCon);
comm.CommandTyp e = CommandType.Sto redProcedure;
comm.Parameters .Add(new SqlParameter("@ NEWID",
System.Data.Sql DbType.Int)).Di rection =
System.Data.Par ameterDirection .Output;

try
{
SqlDataReader sqlRead = comm.ExecuteRea der();
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_CREAT ED)values(getda te()+'')
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****@sommarsk og.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_CREAT ED)values(getda te()+'')
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.dew rote in message
news:44******** *************@r eader.news.celo x.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_CREAT ED)values(getda te()+'')
set nocount off
select @NEWID = @@IDENTITY
end

My C# code:
SqlCommand comm = new SqlCommand("Ins ertProduct", sqlCon);
comm.CommandTyp e = CommandType.Sto redProcedure;
comm.Parameters .Add(new SqlParameter("@ NEWID",
System.Data.Sql DbType.Int)).Di rection =
System.Data.Par ameterDirection .Output;

try
{
SqlDataReader sqlRead = comm.ExecuteRea der();
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