我怎么能得到SCOPE_IDENTITY()从SP

Question

我试图得到SCOPE_IDENTITY到我的vb。. NET应用程序中的存储过程但是我得到DBNull异常。

这是我的SP:

ALTER PROCEDURE [dbo].[sp_XYZ] 
@ID INT,
@OtherID INT OUTPUT
AS
BEGIN
SELECT 
@Name = name,
FROM dbo.PERSON
WHERE  id = @ID
INSERT INTO [SERVERSOLMARTSW].TSWDATA.dbo.t_Employees
    (
    Name
    )
VALUES
    (
    @Name
    )
    SET @OtherID = SCOPE_IDENTITY() 
END

---

vb。NET代码:

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("CON").ConnectionString)
    Dim dataAdapter As New SqlDataAdapter("sp_XYZ", con)
    Dim ARLI As Integer
    Try
        con.Open()
        With dataAdapter.SelectCommand
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add("@ID", SqlDbType.VarChar).Value = id
            Dim identity As New SqlParameter("@OtherID", SqlDbType.Int)
            valorInteger.Direction = ParameterDirection.Output
            .Parameters.Add(identity)
            .ExecuteNonQuery()
            ARLI = identity.Value
        End With
End try

插入语句运行正常。

问题是我得到"转换从类型'DBNull'到类型'Integer'是无效的。"

我正在使用链接服务器，这可能是问题吗?

Answer 1

尝试使用SCOPE_IDENTITY()代替SCOPE_IDENTITY。