.net 获取存储过程返回值和Output输出参数值

1.获取Return返回值

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
conn.Open();
SqlCommand MyCommand = new SqlCommand("SQL", conn);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
MyCommand.Parameters["@id"].Value = 1;
MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.vachar));
MyCommand.Parameters["@name"].Value = 10;
MyCommand.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
MyCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
MyCommand.ExecuteNonQuery();
Response.Write(MyCommand.Parameters["@return"].Value.ToString());

2.获取Output输出参数值

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
MyCommand.Parameters["@id"].Value = 1;
MyCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.varchar));
MyCommand.Parameters["@name"].Value = 10;
MyCommand.Parameters.Add(new SqlParameter("@out_num", SqlDbType.Int));
MyCommand.Parameters["@out_num"].Direction = ParameterDirection.Output;
MyCommand.ExecuteNonQuery();
Response.Write(MyCommand.Parameters["@out_num"].Value.ToString());

----------------------存储过程共有三种返回值 (sql 中的存储过程)-------------------
/*（一）、参数返回值@parm2；
（二）、过程返回值return 100；
（三）、数据集返回值select * from table_1  where age = @parm1
请问，如何得到三类返回值？
*/
create table table_1(

id int,
name varchar(30),
age integer,

)

create procedure proc1
@parm1 integer,
@parm2 varchar(30) output
as
select @parm2 = 'tycoon'
select * from table_1 where age = @parm1
return 100

--（一）、参数返回值@parm2；
declare @re varchar(30)
exec proc1 1,@re out
select 返回结果=@re

--（二）、过程返回值return 100；
declare @re int,@parm2 varchar(30)
exec @re=proc1 1,@parm2 out
select 返回结果=@re

实例：

GO

CREATE PROCEDURE [dbo].[sp_tycoon_CreateUser]
@UserName varchar(50),
@Password varchar(20),
@TrueName varchar(50),
@Sex char(2),
@Phone varchar(20),
@Email varchar(100),
@EmployeeID int,
@DepartmentID varchar(15),
@Activity bit,

-- 输出参数
@UserID int output,
AS
    INSERT INTO tycoon_Users(UserName, Password, TrueName, Sex, Phone, Email,
        EmployeeID, DepartmentID, Activity, UserType,Style)
    VALUES(@UserName, @Password, @TrueName, @Sex, @Phone, @Email,
        @EmployeeID, @DepartmentID, @Activity, @UserType)
    
    SET @UserID = @@IDENTITY
// 返回值
    RETURN 1


GO

 

转载于:https://www.cnblogs.com/northeastTycoon/archive/2012/11/29/2794630.html