Scope_Identity() Always Returning 0

SCOPE_IDENTITY() always returning 0

It looks like you are using ExecuteScalar() and not looking at the output parameter to get the identity. Either look at the value of the output parameter after execution, or select SCOPE_IDENTITY() as the result of your query.

Here's a quick example which should work with ExecuteScalar():

CREATE PROCEDURE dbo.QuickExample

    @Name VARCHAR( 50 )

AS

INSERT INTO dbo.MyTable( Name )
    VALUES( @Name );

SELECT SCOPE_IDENTITY();

GO

Here's how to create a stored procedure with an output parameter:

CREATE PROCEDURE dbo.QuickExample

    @Name   VARCHAR( 50 ),
    @Id INT OUTPUT

AS

INSERT INTO dbo.MyTable( Name )
    VALUES( @Name );

SET @Id = SCOPE_IDENTITY();

GO

If you use an output parameter, you must specify it as one when you call the stored procedure. After calling the stored procedure, the parameter will be populated with the value set in the stored procedure.

SQL Scope_Identity is returning 0 or -1

ExecuteScalar 'Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored'

If you select @fileID (instead of return) it should work.

Alternatively you could access the @fileID parameter value after you execute the query, in which case there's no real point having an ExecuteScalar, you could change it to to ExecutenonQuery.

SCOPE_IDENTITY() return 1

thanks for all.
i finally found the solution.

i use already any from above solutions,

but the query eqcutemode is NonQuery ,so it give me the count of inserted rows
i changed it to Scalar

SELECT SCOPE_IDENTITY() Always Returns 1

Best way to write it is ..

RETURN  SCOPE_IDENTITY()  

You can also use @@IDENTITY and IDENT_CURRENT for this

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Therefore RETURN SCOPE_IDENTITY() is the best one.

SCOPE_IDENTITY() always return NULL if used with SQLBindParameter

The solution for this problem is to use "OUTPUT" clause in order to return member of inserted item instead of calling SELECT SCOPE_IDENTITY();

so the completely sql command is INSERT INTO [dbo].[Test] ([Name], [Position]) OUTPUT INSERTED.[Id] VALUES (?,1);

If I use scope_identity() ; is it possible that it can return the value of a wrong row if several users are using the application at the same time

Using OUTPUT statement you can do it in one query or create procedure for this

INSERT INTO survey (id, title, detail, employerid, userid) 
OUTPUT @title, @detail, inserted.ID INTO surveyquestioncategory (title, detail, surveyid)
VALUES (@id, @title, @detail, @eid, @uid);

From MSDN OUTPUT Clause (Transact-SQL)

---

Related Topics