How to return the value of Identity element

Question

I have a table in SQL Server with 3 columns Id (int identity), email (nvarchar(50)), password (nvarchar(50)). Now I want to write a query where I can insert email and password and that time, I want to return the identity element for id.

For e.g I insert abc@dal.ca and password then the identity element value should be returned.

I wrote it as:

@email nvarchar(50), @password nvarchar(50), @id int

insert into addEmail(email, password) 
values(@email,@password)

return @id

Is this proper ?? How should I do ? How should I check whether this is working properly or not ? If I select

dbo.sp_addEmailReturnId abc@dal.ca, demo

and click on execute, it shows

Incorrect syntax near '.'.

I am unable to find the error. I am just trying to insert email id and password so that could be inserted and i would get the identity element which is automatically incremented by 1 with every new row.

In code part for asp.net, how would I retrieve the id. ?

You can check this [Link](http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) — ViSu, May 20 '13 at 05:05
you can get auto incremented id by `SCOPE_IDENTITY()`. it will give last incremented id. so if there is any trigger called on insert then it may give wrong value.. — Sachin, May 20 '13 at 05:06
I think you should use Damith answer to avoid any misbehaviour of application. As advised by @Sac you might get return wrong value in certain circumstances — Rajeev Kumar, May 20 '13 at 05:19
If you're calling a stored procedure to do the INSERT, and if you have the ability to modify that stored procedure, you can add an OUT parameter [see `ParameterDirection`] to your command to hold the autoincremented id; assign SCOPE_IDENTITY() to that parameter's value; back on the client you can get the id value from the parameter. — Tim, May 20 '13 at 11:00

score 1 · Answer 1 · answered May 20 '13 at 05:05

1

try

insert into addEmail(email,password) 
OUTPUT INSERTED.ID
values(@email,@password)

answered May 20 '13 at 05:05

Damith

62,401
13
102
153

If I write query as----- insert into addEmail(email,password) values(@email,@password) SELECT SCOPE_IDENTITY() would it mean same ?? – user2387900 May 20 '13 at 07:04
both are same, but you don't need two lines with this way – Damith May 20 '13 at 07:17

score 1 · Answer 2 · answered May 20 '13 at 10:51

1

Try this query; it will fetch you the id

insert into addEmail(email, password) values(@email,@password) Select @@IDENTITY;

answered May 20 '13 at 10:51

kavita verma

75
1
10

Rajeev Kumar · Accepted Answer · 2013-05-20T07:18:05.840

0

Do like this

 private int getEmail(string email, string password) 
 { 
   string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; 
   using (SqlConnection conn = new SqlConnection(cs)) 
   {  
     conn.Open(); 
     SqlCommand cmd = new SqlCommand("sp_addEmailReturnid", conn); 
     cmd.CommandType = CommandType.StoredProcedure; 
     cmd.Parameters.AddWithValue("@email", email); 
     cmd.Parameters.AddWithValue("@password",password); 
     int nUploadId = Convert.ToInt32(cmd1.ExecuteScalar()); } // Updated Part

NOTE

At the end of your stored proc i.e after insert statement add

Select Scope_Identity()

Edit

Your proc would be sometihng like this

ALTER proc [dbo].[sp_addEmailReturnid]  
    @email                  VARCHAR(500),
    @password       VARCHAR(500)        
AS
BEGIN
 // Your insert statement here

 Select Scope_Identity()

End

edited May 20 '13 at 07:18

answered May 20 '13 at 05:01

Rajeev Kumar

4,901
8
48
83

If I write query as----- insert into addEmail(email,password) values(@email,@password) SELECT SCOPE_IDENTITY() would it mean same ?? – user2387900 May 20 '13 at 05:32
The problem I am getting is, when i write the code in asp.net and call the store procedure, it says "procedure has too many arguments specified." I think it returns two things , one is the id number and other is " 1 row affected( as i insert the things also) ". How can i solve this ?? – user2387900 May 20 '13 at 06:54
private int getEmail(string email, string password) { string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; using (SqlConnection conn = new SqlConnection(cs)) { conn.Open(); SqlCommand cmd = new SqlCommand("sp_addEmailReturnid", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@password",password); return cmd.ExecuteNonQuery(); } – user2387900 May 20 '13 at 07:01
sorry,, i am unable to edit the code which i just wrote. Sorry for the convinience. – user2387900 May 20 '13 at 07:03
@user2387900 If you still face any issue. Feel free to ask more – Rajeev Kumar May 20 '13 at 07:07
int nUploadId = Convert.ToInt32(cmd.ExecuteScalar()); return nUploadId; I wrote this and still I am getting same error. IN SP,, I didnt got should I make any change or not.. ? It is: insert into addEmail(email,password) values(@email,@password) SELECT SCOPE_IDENTITY() – user2387900 May 20 '13 at 07:10
@user2387900 you needn't to return anything here. Remove `nUploadId` from code as well as any return statement from stored proc as well – Rajeev Kumar May 20 '13 at 07:11
No I want that identity element, I need to pass that identity element in other function. SO, it is mandatory to return identity element and pass it in other function. – user2387900 May 20 '13 at 07:14
well that need some sense.. You change it as per your variable name. Also remove any variable you declare in stored proc to return value – Rajeev Kumar May 20 '13 at 07:14
Dear you will get that identity element in this line `int nUploadId = Convert.ToInt32(cmd1.ExecuteScalar()); ` – Rajeev Kumar May 20 '13 at 07:15
yea..i will get that identity element in this line,, so i am just returning nUploadId, as i have made return type int. Bro, i dont know where I am wrong. – user2387900 May 20 '13 at 07:19
@user2387900 Show me your stored proc or either look at sample stored proc that i have added in my answer – Rajeev Kumar May 20 '13 at 07:20
ok..I got it...thanks alot...help appreciated... Could u explain me the line: int nUploadId = Convert.ToInt32(cmd1.ExecuteScalar()); So, here it wont take the 1 row affected value ?? what it would do ? – user2387900 May 20 '13 at 07:23
It will return the value form `Select Scope_Identity()` You can read more about it from google – Rajeev Kumar May 20 '13 at 07:26

score 0 · Answer 4 · answered May 20 '13 at 05:02

0

Use Scope_Identity

insert into addEmail(email,password) values(@email,@password)

SELECT SCOPE_IDENTITY()

answered May 20 '13 at 05:02

arunlalam

1,838
2
15
23

The problem I am getting is, when i write the code in asp.net and call the store procedure, it says "procedure has too many arguments specified." I think it returns two things , one is the id number and other is " 1 row affected( as i insert the things also) ". How can i solve this ?? – user2387900 May 20 '13 at 06:54

How to return the value of Identity element

4 Answers4