SqlServer如何获取存储过程的返回值

1.Output参数返回值

1 CREATE PROCEDURE [dbo].[upInformation](  2 @age int ,  3 @id bigint OUTPUT  4 )  5 AS  6 BEGIN  7 SET NOCOUNT ON;  8 BEGIN  9 INSERT INTO [Information](age ) 10 VALUES (@age ) 11 SET @id = @@IDENTITY 12 END 13 END

存储过程中获得方法：

1 DECLARE @age int 2 DECLARE @id bigint 3 EXEC [upInformation] @age,@id output

2.Return过程返回值

1 CREATE PROCEDURE [dbo].[upInformation](  2 @age int ,  3 @id bigint OUTPUT  4 )  5 AS  6 BEGIN  7 SET NOCOUNT ON;  8 IF(EXISTS(SELECT * FROM [Shop] WHERE [s_id] = @age ))  9 BEGIN 10  INSERT INTO [Information](age ) VALUES (@age )  11  SET @id = @@IDENTITY  12  RETURN 1 — 插入成功返回1  13 END  14 ELSE  15  RETURN 0 — 插入失败返回0  16 END

存储过程中获得方法：

1 DECLARE @age int 2 DECLARE @id bigint 3 DECLARE @result bit 4 EXEC @result = [upInformation] @age ,id output

3.Select数据集返回值

1 CREATE PROCEDURE [dbo].[upInformation]( 2 @id int 3 ) 4 AS 5 BEGIN 6 SET NOCOUNT ON; 7 SELECT id,age FROM [Information] 8 WHERE id = @id 9 GO

存储过程中获得方法：(使用临时表)

1 CREATE TABLE [dbo].[Temp]( 2 [id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 3 [age] [int] NOT NULL 4 ) 5 INSERT [Temp] EXEC [nb_order_select] @id 6 – 这时 Temp 就是EXEC执行SELECT 后的结果集 7 SELECT * FROM [Temp] 8 DROP [Temp] — 删除临时表

C#获取Return返回值

1 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());  2 conn.Open();  3 SqlCommand MyCommand = new SqlCommand("upInformation", conn);  //存储过程名字  4 MyCommand.CommandType = CommandType.StoredProcedure;   //指定类型为存储过程  5 MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));  6 MyCommand.Parameters["@a"].Value = 10;  7 MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));  8 MyCommand.Parameters["@b"].Value = 20;  9 MyCommand.Parameters.Add(new SqlParameter("@return", SqlDbType.Int)); 10 MyCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue; 11 MyCommand.ExecuteNonQuery();                //执行存储过程 12 Response.Write(MyCommand.Parameters["@return"].Value.ToString()); //取得return的返回值

C#获取Output输出参数值

1 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());  2 conn.Open();  3 SqlCommand MyCommand = new SqlCommand("upInformation", conn);  4 MyCommand.CommandType = CommandType.StoredProcedure;  5 MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));  6 MyCommand.Parameters["@a"].Value = 20;  7 MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));  8 MyCommand.Parameters["@b"].Value = 20;  9 MyCommand.Parameters.Add(new SqlParameter("@c", SqlDbType.Int)); 10 MyCommand.Parameters["@c"].Direction = ParameterDirection.Output; 11 MyCommand.ExecuteNonQuery(); 12 Response.Write(MyCommand.Parameters["@c"].Value.ToString()); //指定取得存储过程的返回值

C#接收存储过程返回值

1  public static int Information(User us)  2      {  3          int iRet;  4          SqlConnection conn = new SqlConnection(Conn_Str);  5          SqlCommand cmd = new SqlCommand("upInformation", conn);  6          cmd.CommandType = CommandType.StoredProcedure;   //指定存储过程  AddWithValue可以指定名称和值，而Add需要指定名称，类型，再给value  7          cmd.Parameters.AddWithValue("@UName", us.UName);  8          cmd.Parameters.AddWithValue("@UPass", us.UPass);  9          cmd.Parameters.AddWithValue("@PassQuestion", us.PassQuestion); 10          cmd.Parameters.AddWithValue("@PassKey", us.PassKey); 11          cmd.Parameters.AddWithValue("@Email", us.Email); 12          cmd.Parameters.AddWithValue("@RName", us.RName); 13          cmd.Parameters.AddWithValue("@Area", us.Area); 14          cmd.Parameters.AddWithValue("@Address", us.Address); 15          cmd.Parameters.AddWithValue("@ZipCodes", us.ZipCodes); 16          cmd.Parameters.AddWithValue("@Phone", us.Phone); 17          cmd.Parameters.AddWithValue("@QQ", us.QQ); 18          cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;   //指定输出参数是返回值     19          try 20          { 21              conn.Open(); 22              cmd.ExecuteNonQuery();                   //执行存储过程 23              iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;    //取得return的值 24          } 25          catch (SqlException ex) 26          { 27              throw ex; 28          } 29          finally 30          { 31              conn.Close(); 32          } 33          return iRet; 34      }

C#接收存储过程的输出参数

1 public static decimal Cart_UserAmount(int UID)  2     {  3         decimal iRet;  4         SqlConnection conn = new SqlConnection(Conn_Str);  5         SqlCommand cmd = new SqlCommand("Cart_UserAmount", conn);  6         cmd.CommandType = CommandType.StoredProcedure;  7         cmd.Parameters.AddWithValue("@UID", UID);  8         cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Direction=ParameterDirection.Output;  //利用Add方法为其添加名称，类型和输出参数  9         try 10         { 11             conn.Open(); 12             cmd.ExecuteNonQuery(); 13             iRet = (decimal)cmd.Parameters["@Amount"].Value;  //取得存储过程中的输出参数 14         } 15         catch (SqlException ex) 16         { 17             throw ex; 18         } 19         finally 20         { 21             conn.Close(); 22         } 23         return iRet; 24     }

C#取得结果集

1 string sqlw = string.Format("exec sp_UserInfo {0}", uid);  2             DataTable dsuser = SqlConn.GetDataSet(sqlw).Tables[0];  3   4  public static DataSet GetDataSet(string sql)  5         {  6             string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ToString();  7             SqlConnection conn = new SqlConnection(connStr);  8             SqlCommand cmd = new SqlCommand(sql, conn);  9             SqlDataAdapter da = new SqlDataAdapter(cmd);  //直接用SqlDataAdapter将结果集取出来放入dataset中 10             DataSet ds = new DataSet(); 11             da.Fill(ds); 12             conn.Close(); 13             cmd.Dispose(); 14             return ds; 15         }