create Procedure GetUserAccount
as
select * from UserAccount
go

-------------执行上面的存储过程----------------
exec GetUserAccount

--相当于执行了select 语句，返回数据集

create Procedure inUserAccount
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)
go

-------------执行上面的存储过程----------------

exec inUserAccount
--修改和插入都只是执行操作，不会返回任何数据

create Procedure inUserAccountRe
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)
return @@rowcount
go

-------------执行上面的存储过程----------------

exec inUserAccountRe
--返回执行多少行

create Procedure GetUserAccountRe
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe '7',null

create Procedure GetUserAccountRe1
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
return @@rowcount
go

exec GetUserAccountRel '7' ,null

create Procedure GetUserAccountRe2
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
select * from UserAccount
return @@rowcount
go

create Procedure GetUserAccountRe3
as
select * from UserAccount
select * from UserAccount where UserID>5
go

-------------执行上面的存储过程----------------

exec GetUserAccountRe3

 //同时具有返回值、输入参数、输出参数的存储过程GetUserAccountRe1
        public void runGetUserAccountRe1()
        {
            SqlCommand cmd = common("GetUserAccountRe1");
            // 创建参数 
            IDataParameter[] parameters = { 
                 new SqlParameter("@UserName", SqlDbType.NChar,20) , 
                 new SqlParameter("@UserID", SqlDbType.Int) ,
                 new SqlParameter("rval", SqlDbType.Int,4) 
             };
            // 设置参数类型 
            parameters[0].Value = "7";
            parameters[1].Direction = ParameterDirection.Output;  // 设置为输出参数 
            parameters[2].Direction = ParameterDirection.ReturnValue;  //设置为返回值
            // 添加参数 
            cmd.Parameters.Add(parameters[0]);
            cmd.Parameters.Add(parameters[1]);
            cmd.Parameters.Add(parameters[2]);
            con.Open();
            // 执行存储过程并返回影响的行数 
            Label1.Text = cmd.ExecuteNonQuery().ToString();
            con.Close();
            // 显示影响的行数和输出参数 
            Label1.Text += "-输出参数为：" + parameters[1].Value.ToString();
            Label1.Text += "-返回值为：" + parameters[2].Value.ToString();

        }