24

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]
Improve this question
0

3 Answers 3

Reset to default
29

  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.

Improve this answer
1
  • 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. Commented Aug 16, 2011 at 21:47
9

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.

Improve this answer
1
  • 3
    A function can't have more than one return type. It will have to use the widest type to include every possibility. Commented Aug 16, 2011 at 22:06
7

"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)

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.