如何从C＃代码中的存储过程中获取@@ Identity的值或选择scope_identity()

Question

i使用SQL Server2012。在此存储过程中，我想获得@@identity或SELECT SCOPE_IDENTITY()的值。intMyIdentity是身份列。

CREATE PROCEDURE [spMyInsert] 
    (@vchVar AS VARCHAR(20), 
     @fltVar AS FLOAT)
AS
    SELECT TOP 1 intMyIdentity 
    FROM MyTbl  
    WHERE vchVar = @vchVar
    IF @@rowcount = 0
    BEGIN
        INSERT INTO MyTbl (vchVar, fltVar) 
        VALUES (@vchVar, @fltVar)
        SELECT @@identity AS intMyIdentity 
        -- SELECT SCOPE_IDENTITY()
    END

如何获得C＃中的@@identity或SELECT SCOPE_IDENTITY()的值？

使用ExecuteScalar时，它返回0，而不是正确的值。

SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
oConn.Open();
mCommand.CommandText = "spMyInsert";
mCommand.Parameters.Add(new SqlParameter("@vchVar", txtMyVar.Text));
mCommand.Parameters.Add(new SqlParameter("@fltVar", dblMyVar));
mCommand.Connection = oConn;
mCommand.CommandType = CommandType.StoredProcedure; 
intMyIdentity = Convert.ToInt32(mCommand.ExecuteScalar());  --> this always return 0
oConn.Close();

编辑：此查询作品

IF NOT EXISTS (SELECT 1 FROM myTbl WHERE vchVar = @vchVar)
BEGIN
    INSERT INTO myTbl (vchVar, fltVar)
    VALUES (@vchVar, @fltVar)
    SELECT SCOPE_IDENTITY()
END

Answer 1

当您使用ExecutesCalar时，您的存储过程必须返回@@ Identity。返回@@ Identity;