从存储过程中检索返回值

我有一个存储过程，如下所示：

CREATE PROCEDURE procTest 
    -- Add the parameters for the stored procedure here
    @gameId int = NULL, 
    @memberID int = NULL,
    @mediatorID int = NULL,
    @id int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO tblSiteChallengeMember (gameId, creatorId, mediatorId, completed, dateAdded) VALUES (@gameId, @memberId, @mediatorID, 0, GETDATE())
END
GO

我需要返回 IDtblSiteChallengeMember，但我不知道该怎么做。

我看过这个例子'在这附近 http://forums.asp.net/post/1987936.aspx，但我担心：

SELECT TOP 1 @ProductName=PRODUCTNAME, @Quantity =quantity 
FROM Products P, [Order Details] OD, Orders O, Customers C 
WHERE C.CustomerID = @CustomerID 
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

不起作用，因为单个用户可以输入多次，并且这些时间可能根本不唯一。

如何获取插入到 ASP.net 页面的最后一行的 ID？

将我的存储过程更新为：

ALTER PROCEDURE [dbo].[procCreateChallengeMember]
    -- Add the parameters for the stored procedure here
    @gameId int = NULL, 
    @memberID int = NULL,
    @mediatorID int = NULL,
    @id int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO tblSiteChallengeMember (gameId, creatorId, mediatorId, completed, dateAdded) VALUES (@gameId, @memberId, @mediatorID, 0, GETDATE())

    SELECT @id = SCOPE_IDENTITY()
END

SQL表是：

GO

/****** Object:  Table [dbo].[tblSiteChallengeMember]    Script Date: 10/17/2012 08:05:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSiteChallengeMember](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [gameId] [int] NULL,
    [creatorId] [int] NOT NULL,
    [mediatorId] [int] NULL,
    [completed] [tinyint] NULL,
    [dateAdded] [datetime] NULL,
 CONSTRAINT [PK__tblSiteD__3213E83F628FA481] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

aspx.cs 是：

SqlConnection db = DataConn.SqlConnection();

db.Open();
SqlTransaction transaction = db.BeginTransaction();

try
{
    int id = 0;

    SqlCommand sqlComm =
            new SqlCommand(
                "procCreateChallengeMember @gameId, @creatorId, @mediatorId, @id", db, transaction) { CommandType = CommandType.Text };

    sqlComm.Parameters.Add(new SqlParameter("@gameId", SqlDbType.Int)).Value = 1;
    sqlComm.Parameters.Add(new SqlParameter("@creatorId", SqlDbType.Int)).Value = 1;
    sqlComm.Parameters.Add(new SqlParameter("@mediatorId", SqlDbType.Int)).Value = 1;
    SqlParameter param = new SqlParameter("@id", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };

    sqlComm.Parameters.Add(param);
    sqlComm.ExecuteNonQuery();

    //if(param.Value != DBNull.Value)
    //{
        id = Convert.ToInt32(param.Value);
        Response.Write("One: " + id + "<br/>");
    //}
    transaction.Commit();
    //Response.Write("Two: " + sqlComm.Parameters["expr"].Value + "<br/>");

}
catch (Exception ess)
{
    transaction.Rollback();
    Response.Write(ess.Message);
}

现在出现错误对象不能从 DBNull 转换为其他类型。（当我删除该 IF 语句时）

“如何获取插入到 ASP.net 页面的最后一行的 ID？”

SELECT @id = SCOPE_IDENTITY()

获取插入行以返回该行的存储过程。

EDIT

回应后续问题。

您需要让您的 SqlCommand 知道参数即将退出。

   // Just below where you added your other params
   SqlParameter outputParam = new SqlParameter("@id", SqlDbType.Int)
   { 
      Direction = ParameterDirection.Output 
   };

   sqlComm.Parameters.Add(outputParam);

   sqlComm.ExecuteNonQuery();