private void btnAdd_Click(object sender, System.EventArgs e)
{
//C#调用SQL存储过程(insert)
try
{
myCnn.Open();
myCmm=new SqlCommand("sp_insert",myCnn);
myCmm.CommandType=CommandType.StoredProcedure;
SqlParameter _name = myCmm.Parameters.Add("@name", SqlDbType.VarChar, 50);
_name.Direction = ParameterDirection.Input;
SqlParameter _addr = myCmm.Parameters.Add("@addr", SqlDbType.VarChar, 50);
_addr.Direction = ParameterDirection.Input;
_name.Value=this.getMD5(this.pName.Text.ToString());
_addr.Value=this.Addr.Text;
myCmm.ExecuteNonQuery();
MessageBox.Show("操作成功","温馨提示");
myCmm.Dispose();
myCnn.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
myCnn.Close();
}
}
SQL 中的存储过程
下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info 存储过程可以通过以下方法执行：
EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
