SQL Output statement in stored procedure

Question

Please help me editing following SQL Server stored procedure to get the ID of last inserted row using an OUTPUT statement so that retrieve it throw C# code behind like

int ID = Convert.ToInt32(insertUser.ExecuteScalar());

Stored procedure code:

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ID INT 

    INSERT INTO dbo.Registration_Table (StudentName, Email, CourseCode, CourseTitle) 
    VALUES (@StudentName, @Email, @CourseCode, @CourseTitle)  

    SELECT 2
END
GO

Procedural code (like stored procedures) is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Aug 27 '17 at 11:24
**SQL** is the query language - but it's just by **most** RDBMS systems - and we really need to know which **CONCRETE** RDBMS system you're using - Oracle? SQL Server? DB2? MySQL? PostgresQL? **SQL** isn't clear enough ... — marc_s, Aug 27 '17 at 11:52
i am using MS SQL. What i need is to get the ID of row inserted back by stored procedure — Mansoor, Aug 27 '17 at 17:47
See here: https://stackoverflow.com/questions/5558582/sql-server-output-clause-into-a-scalar-variable — RBarryYoung, Aug 27 '17 at 18:31

score 1 · Answer 1 · answered Aug 27 '17 at 14:25

1

you should add a 'Transact-SQL' tag to this post.

The answer is

select SCOPE_IDENTITY()

although for completeness, i should also mention that you can use the output clause in the insert statement, but that would imo only be useful if you insert more than one row.

answered Aug 27 '17 at 14:25

nico boey

389
1
5

i am using MS SQL. What i need is to get the ID of row inserted back by stored procedure. Scope identity work for me but i want to get the result by OUTPUT – Mansoor Aug 27 '17 at 17:49
1

@Mansoor: but if this approach with `SCOPE_IDENTITY()` works - why do you insist on using `OUTPUT` ?? – marc_s Aug 27 '17 at 18:25
SCOPE_IDENTITY() may give error as i read. i need the ID of particular row inserted by that stored procedure – Mansoor Aug 28 '17 at 00:44
If your insert statement inserts more than one record, the optimizer may execute it in parallel, in which case SCOPE_IDENTITY may not return the exact last record id. In your statement, only one record is inserted, and SCOPE_IDENTITY will return the last id. – nico boey Aug 28 '17 at 08:29

score 0 · Answer 2 · answered Aug 28 '17 at 01:26

0

Finally i made it. if anyone want id of last inserted column through stored procedure using OUTPUT clause just use following code. SCOPE_IDENTITY() may lead to error during parallel insertion by multiple users

AS
BEGIN
    SET NOCOUNT ON;
     DECLARE @IDs TABLE(ID INT);

    INSERT INTO dbo.Registration_Table(StudentName,Email,CourseCode,CourseTitle)
  OUTPUT inserted.ID INTO @IDs(ID) 
    VALUES (@StudentName, @Email, @CourseCode, @CourseTitle)  

   SELECT ID FROM @IDs;
END
GO

answered Aug 28 '17 at 01:26

Mansoor

31
1
8

Can you reference where you read that SCOPE_IDENTITY may cause an error with multiple users? I've never had any problem. – Nick.Mc Aug 28 '17 at 01:53
Nick , please folow this link https://support.microsoft.com/en-in/help/2019779/you-may-receive-incorrect-values-when-using-scope-identity-and-identit – Mansoor Aug 29 '17 at 11:06

SQL Output statement in stored procedure

2 Answers2