从存储过程返回值 [英] Return Value from a Stored Procedure

问题描述

我想从我的存储过程返回的数据。这是什么我都试过，绝对没有什么回来

存储过程：

 声明@Submit_ID为int 
（从Sub_Details选择（MAX（Sub_ID）+ 1））设置@Submit_ID =; 
 INSERT INTO ..​​... 
返回@Submit_ID 
  

C＃（2.0 ）

  SqlConnection的康恩= NULL; 
 INT子ID; 
试
 {
康恩=新的SqlConnection（conn_string）; 
的SqlCommand CMD =新的SqlCommand（INSERT_INTO_MYDB，康恩）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 
的SqlParameter P1 =新的SqlParameter（@名字，SqlDbType.NVarChar，255）; 
 p1.Direction = ParameterDirection.Input; 
 p1.Value = txtFirstName.Text; 
 cmd.Parameters.Add（P1）; 
 
的SqlParameter P2 =新的SqlParameter（@姓，SqlDbType.NVarChar，255）; 
 p2.Direction = ParameterDirection.Input; 
 p2.Value = txtLastName.Text; 
 cmd.Parameters.Add（P2）; 
 
的SqlParameter PSUB =新的SqlParameter（@ Sub_ID，SqlDbType.Int）; 
 pSub.Direction = ParameterDirection.ReturnValue; 
 
 conn.Open（）; 
 cmd.ExecuteNonQuery（）; 
 
子ID = Convert.ToInt32（PSUB）; 
} 
  

解决方案

首先添加参数 CMD ，并使用 .value的它的值

  cmd.Parameters.Add（PSUB）; //参数添加到命令
 cmd.ExecuteNonQuery（）; 
子ID = Convert.ToInt32（pSub.Value）; //使用.value的
  

I am trying to get data returned from my stored procedure. This is what I have tried and absolutely nothing is coming back.

Stored Proc:

Declare @Submit_ID as int
Set @Submit_ID = (Select (Max(Sub_ID) + 1) from Sub_Details);
INSERT INTO.....
Return @Submit_ID

C# (2.0)

SqlConnection conn = null;
int subid;
try
{
    conn = new SqlConnection(conn_string);
    SqlCommand cmd = new SqlCommand("INSERT_INTO_MYDB", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter p1 = new SqlParameter("@FirstName", SqlDbType.NVarChar, 255);
    p1.Direction = ParameterDirection.Input;
    p1.Value = txtFirstName.Text;
    cmd.Parameters.Add(p1);

    SqlParameter p2 = new SqlParameter("@LastName", SqlDbType.NVarChar, 255);
    p2.Direction = ParameterDirection.Input;
    p2.Value = txtLastName.Text;
    cmd.Parameters.Add(p2);

    SqlParameter pSub = new SqlParameter("@Sub_ID", SqlDbType.Int);
    pSub.Direction = ParameterDirection.ReturnValue;

    conn.Open();
    cmd.ExecuteNonQuery();

    subid = Convert.ToInt32(pSub);
}

解决方案

First add parameter to cmd and use .Value to its value

 cmd.Parameters.Add(pSub);    // add parameters to command
 cmd.ExecuteNonQuery();
 subid = Convert.ToInt32(pSub.Value); // Use .Value

这篇关于从存储过程返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！