Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.

INSERT INTO Customers (
         Type, Name, Address, ContactName, 
         ContactNumber, ContactEmail, Supplier)
VALUES (
         @Type, @Name, @Address, @ContactName, 
         @ContactNumber, @ContactEmail, @Supplier)

SELECT @@IDENTITY

The call to connection.Query<int>(sql, ...) is throwing an Invalid Cast Exception. I've debugged it and it's at the point where Dapper calls GetValue on the returned SqlDataReader.

The return type of GetValue is Object, inspecting it in the debugger show's it's a boxed decimal.

If I change the select to SELECT CAST(@@IDENTITY as int), the return of GetValue is a boxed int and the exception isn't thrown.

The Id column is definitely of type int; Why would SELECT @@IDENTITY return a decimal?

Some additional information:

  • The database is brand new.
  • The Customers table is the only object I've added to it. There are no other (user) tables, views, triggers or stored procedures in the database.
  • There are 10 rows in the database, there Id's are 1,2,3,4,5,6,7,8,9,10 (i.e. the column isn't beyond the limits of an int).

My table definition is

CREATE TABLE [dbo].[Customers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Address] [nvarchar](1000) NOT NULL,
    [ContactName] [nvarchar](255) NOT NULL,
    [ContactNumber] [nvarchar](50) NOT NULL,
    [ContactEmail] [nvarchar](255) NOT NULL,
    [Supplier] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
share|improve this question
    
Do you have any triggers on the Customers table? –  Richard Aug 16 '11 at 21:05
    
I would use SCOPE_IDENTITY() instead of @@IDENTITY. @@IDENTITY will give you the last identity value created by anything on the current connection, as opposed to in your current scope. So, as Richard suggested, a trigger modifying another table and generating an identity would affect the return of @@IDENTITY. –  Nick Chammas Aug 16 '11 at 21:12
    
There are no triggers in the DB. It's a fresh database and the Customers table is the only table I've created. –  Greg B Aug 16 '11 at 21:34
    
@Greg B: this is the function's return type. Did you expect int/bigint as the return type (as the question suggests) or are you questioning the MS choice for this function? –  Marian Aug 16 '11 at 21:37

3 Answers 3

up vote 22 down vote accepted
  1. @@identity returns a numeric(38,0). You'll need to cast it to get it to an int.

    SELECT CAST(@@identity AS INT)

  2. Also, try using scope_identity instead. If you have any triggers on the Customers table, you may end up getting the last identity from another table.

  3. Finally, since you're using dapper, you'll want to wrap all of that inside a stored procedure so that you are guaranteed to execute the insert and then the select on the identity in the same batch.

    Theoretically, it should work most of the time to execute both of those on their own. But issues could arise if you have to go to the database twice. (E.g. how does this work with connection pooling? What about dropped connections? etc.) If you just throw it all in a stored procedure, you won't have to worry about that extra effort down the road.

share|improve this answer
    
Thanks for #3. Is there no way to define a batch in an adhoc SQL statement? –  Greg B Aug 16 '11 at 21:44
    
I was taking another look at it. If you include all the statements in one call, it is all one batch. If you break the statements up into separate calls, things could get buggy. –  Richard Aug 16 '11 at 21:47
3  
+1 for recommending SCOPE_IDENTITY() –  Andrew Bickerton Aug 17 '11 at 11:20

Create table says:

"IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

seed

Is the value used for the very first row loaded into the table.

increment

Is the incremental value added to the identity value of the previous row loaded."

So the system function @@identity will have to cope with the most covering type.

share|improve this answer
    
And this is why it returns numeric as it's got the widest range..? Thanks –  Greg B Aug 16 '11 at 21:48
3  
A function can't have more than one return type. It will have to use the widest type to include every possibility. –  Marian Aug 16 '11 at 22:06

"Why would SELECT @@IDENTITY return a decimal"

Because it might be too large to fit in an int - it does not match the type of the identity column but as Richard says returns a numeric(38,0) (numeric and decimal are synonyms)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.