存储过程的书写格  

CREATE   PROCEDURE   [拥有者.]存储过程名[;程序编号]  
[(参数#1,…参数#1024)]  
[WITH  
{RECOMPILE   |   ENCRYPTION   |   RECOMPILE,   ENCRYPTION}  
]  
[FOR   REPLICATION]  
AS   程序行

*注  with   {recompile|encryption}
        recompile:表示每次执行此存储过程时都重新编译一次
        encryption:所创建的存储过程的内容会被加密

*存储过程名不能超过128个字。每个存储过程中最多设定1024个参数

 

在sql sever2005数据库中执行以下代码

           create procedure my_cp
          @CustomerName varchar(50),
          @CustomerID varchar(50),
          @Address varchar(50) output
          as
          SELECT * FROM [TM_Customer] where [Name]like '%'+@CustomerName+'%'
          and [Customer ID] like '%'+@CustomerID+'%';
          SELECT @Address = Address FROM [TM_Customer] where [Name]like '%'+@CustomerName+'%'
          and [Customer ID] like '%'+@CustomerID+'%';
          SET @Address = @Address + '------上海'

.net中代码

             string conString = "Data Source=.;Initial Catalog=text;Integrated Security=True;max pool size=300";
            SqlConnection sqlConn = new SqlConnection(conString);
            sqlConn.Open();
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = sqlConn;
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "my_cp";

            SqlParameter para = sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar, 50); //参数1
            para.Value = "地铁票务";
            SqlParameter para1 = sqlCmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 50);  //参数2
            para1.Value = "002";
            SqlParameter para2 = sqlCmd.Parameters.Add("@Address", SqlDbType.VarChar, 50);     //参数3
            para2.Direction = ParameterDirection.Output;   //因为参数3只是存储过程的输出
            //para2.Value = "aa";   因为是ParameterDirection.Output所以参数3不需要传入值
            //注：para2==sqlCmd.Parameters["@Address"]

            DataTable dt = new DataTable();
            SqlDataReader data = sqlCmd.ExecuteReader();
            dt.Load(data);

            dataGridView1.DataSource = dt;
            textBox1.Text = Convert.ToString(para2.Value);