How can i get the row id that inserted via stored procedure?

Question

Model model=new Model();
model.id=0;
model.name="";
model.surname="";
db.Model.Add(model);
db.SaveChanges();
int id_returned=model.id;

This block gives me the id field of the inserted row.

But i use stored procedure for this insert process.

Model model=new Model();
model.id=0;
model.name="";
model.surname="";
int returned_id2=db.Sp_Model_insert(model.name, model.surname);

this block inserts the row. returned_id2 returns -1.

How can i get the row id that inserted via Sp ?

Here is my Stored Procedure:

USE [KayaShop]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [KS_ACCOUNTS].[SP_AccountModules_Insert](
@Area nvarchar(50),
@Controller nvarchar(50),
@Action nvarchar(50),
@SubAction nvarchar(50),
@Name nvarchar(50),
@TopName nvarchar(50),
@Level int,
@Visible int,
@Clickable int,
@HomePage int,
@Icon nvarchar(20),
@Status int
)
AS 
BEGIN 
 SET NOCOUNT ON 
 INSERT INTO [KS_ACCOUNTS].[AccountModules]
       ([Area]
       ,[Controller]
       ,[Action]
       ,[SubAction]
       ,[Name]
       ,[TopName]
       ,[Level]
       ,[Visible]
       ,[Clickable]
       ,[HomePage]
       ,[Icon]
       ,[Status])
 VALUES
       (@Area,
       @Controller,
       @Action,
       @SubAction,
       @Name,
       @TopName,
       @Level,
       @Visible,
       @Clickable,
       @HomePage,
       @Icon,
       @Status)
END

try this http://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar — Ankit, Nov 23 '16 at 06:30
It looks like you're using Entity Framework or some other ORM. If so, you should not perform the `INSERT` by yourself, let your ORM do it for you so you can take advantage of its state-tracking. Your ORM will automatically map and assign generated `IDENTITY` values back to your entity objects. — Dai, Nov 23 '16 at 06:31
@dai yes. you are right. also i told this entity feature as you can see above. but i need to insert via SP. — ysrtymz, Nov 23 '16 at 06:42
@ysrtymz Why do you need to insert via Stored Procedure? What is the business-case? You don't need to restrict yourself to Sproc-based inserts for security, for example, as SQL Server lets you grant/deny access to individual DML statements, and auditing is better performed with triggers, too. — Dai, Nov 23 '16 at 06:44
They told me that inserting via SP is faster than inserting without SP. thats why i use sp — ysrtymz, Nov 23 '16 at 06:51
@ysrtymz They're wrong. Sprocs are not faster at inserting than any other SQL (and the *really* faster way is `BULK INSERT` :D ). There is a long-running myth that Sprocs are faster than raw SQL - this dates back from earlier versions of SQL Server that cached execution plans (only for `SELECT` statements, I note), this hasn't been true since at least SQL Server 2000, so their information is both incorrect and 16 years out of date! — Dai, Nov 23 '16 at 06:53

score 1 · Answer 1 · edited Feb 21 '17 at 20:02

Multiple ways:

SCOPE_IDENTITY() - https://msdn.microsoft.com/en-us/library/ms190315.aspx - Returns the last single IDENTITY value in the current session's current scope.
@@IDENTITY - https://msdn.microsoft.com/en-us/library/ms187342.aspx - Returns the last single IDENTITY value in the current session (regardless of scope).
INSERT INTO ... VALUES ... OUTPUT - https://msdn.microsoft.com/en-us/library/ms177564.aspx - outputs all inserted values, including IDENTITY values, this is the only way to retrieve IDENTITY values when your INSERT statement will insert multiple rows.

As your stored procedure only seems to insert a single row, you should be fine with SCOPE_IDENTITY.

Note that if all your stored procedure does is INSERT you probably don't need a stored procedure, your database client code should perform the INSERT directly.

Note that you should not use the RETURN keyword to return the IDENTITY value to the caller, you should use output parameters - a stored procedure's return-value is intended to convey status information and it is restricted to just an int value (e.g. return 0 for success).

i am very new to stored procedures. can you answer more specific please ? — ysrtymz, Nov 23 '16 at 06:28

score 1 · Answer 2 · answered Nov 23 '16 at 06:49

1

SELECT SCOPE_IDENTITY()

Add this after the insert query in your stored procedure

answered Nov 23 '16 at 06:49

Noxious Reptile

838
1
7
24

score 0 · Answer 3 · answered Nov 23 '16 at 08:16

I added these lines to the end of my sp:

SELECT @Id =SCOPE_IDENTITY()
RETURN

and at my code side:

int Id = 0;
ObjectParameter prm = new ObjectParameter("Id", typeof(int));
DB.SP_AccountModules_Insert(DTO.Area, DTO.Controller, DTO.Action, DTO.SubAction, DTO.Name, DTO.TopName, DTO.Level, DTO.Visible, DTO.Clickable, DTO.HomePage, DTO.Icon, DTO.Status, prm);
Id = Convert.ToInt32(prm.Value);
return Id;

i send and out parameter from my code to my sp, then i assign the last inserted id to my parameter and return it to my code. thats it. Thank you for all help.

How can i get the row id that inserted via stored procedure?

3 Answers3