sql序列（4）存储过程

1,语法：

CREATE PROC PRO_USERS_LIST
AS
<T-SQL>
GO

EXEC PRO_USERS_LIST

 

2，创建存储过程：
语句1：
=====================================================

CREATE PROC PRO_USERS_LIST
AS
SELECT * FROM USERS
GO

EXEC PRO_USERS_LIST

 

语句2：带输入参数的存储过程
=====================================================

CREATE PROC PRO_USERS_LIST
@ID INT
AS
SELECT * FROM USERS WHERE Id = @ID
GO

EXEC PRO_USERS_LIST 2

语句3：带输出参数
=====================================================

CREATE PROC PRO_USERS_LIST
@ID INT,
@COUNT INT OUTPUT
AS
SELECT * FROM USERS WHERE Id = @ID;
SELECT @COUNT = COUNT(1) FROM USERS; 
RETURN @COUNT;
GO


DECLARE @return_value INT 
EXEC = PRO_USERS_LIST 2 ,@return_value OUTPUT

SELECT @return_value

3， 修改存储过程：
语句4：ALTER关键字
=======================================================

ALTER PROC PRO_USERS_LIST
AS
SELECT * FROM Users
GO

4,查看一个存储过程的定义
语句5：
=======================================================

EXEC sp_helptext PRO_USERS_LIST

【sp_helptext 对表对象不起作用】

5，重命名存储过程：
语句6：
=======================================================

EXEC sp_rename PRO_USERS_LIST , PRO_USERS_DETAIL

【sp_rename适用于很多对象，如表、字段等】

6，C#使用存储过程：
语句7：调用无参存储过程
=======================================================

using(SqlConnection conn = new SqlConnection('数据库连接串'))
{
  conn.Open();
  using(SqlCommand cmd = conn.CreateCommand())
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText ="PRO_USERS_DETAIL";
    using(SqlDataReader sdr = cmd.ExecuteReader())
    {
      while(sdr.Read())
      {
       Console.WriteLine("{0}",rdr[0].ToString());
      }
    }

  }
}

语句8：调用有参存储过程：(未测试)
=========================================================

using(SqlConnection conn = new SqlConnection('数据库连接串'))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText ="PRO_USERS_DETAIL";
SqlParameter sp = cmd.Parameters.Add("@ID",SqlDbType.Int);//传入参数
sp.Direction = ParameterDirection.Input;//表示参数为传入参数
sp.Value = 2;
SqlParameter sp_out = cmd.Parameters.Add("@COUNT",SqlDbType.Int);
sp_out.Direction = ParameterDirection.Output;//表示参数为输出参数
SqlParameter sp_return_value = cmd.Parameters.Add("return_value",SqlDbType.Int);
sp_out.Direction = ParameterDirection.ReturnValue;//接收参数
using(SqlDataReader sdr = cmd.ExecuteReader())
{
while(sdr.Read())
{
Console.WriteLine("{0}",rdr[0].ToString());
}
Console.WriteLine("The Output Parameter is {0}", cmd.Parameters["@COUNT"].Value);
Console.WriteLine("The Return Value is {0}", cmd.Parameters["return_value"].Value);
}

}
}

 

7,删除存储过程：
语句9：
==========================================================

DROP PROC PRO_USERS_LIST