带参数的存储过程:默认参数、输出参数、返回值
/*
==============================================
-- Author: xuminghui
-- Create date: 2011/03/28
-- Description: 带默认参数,输出参数和返回值的存储过程
-- =============================================
CREATE PROCEDURE ProcedureTest
@myName varchar( 20 ) output,
@myAge int = 33
AS
BEGIN
SET NOCOUNT ON;
set @myName='xuminghui'
return 1978+@myAge
END
GO
*/
private static void SqlCommandWithComplexParameters()
{
string connect = " Data Source=.\\sqlexpress; Initial Catalog=NorthWind ; Integrated Security = true; " ;
// 准备Connection对象和Command对象:设置Command对象调用存储过程
SqlConnection cn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand( " ProcedureTest " , cn);
cmd.CommandType = CommandType.StoredProcedure;
// 设定参数:输出参数-参数名称必须和存储过程输出参数名一致;
// 返回值参数-参数名称任意、修改默认参数
SqlParameter paramMyName = cmd.Parameters.Add( " @myname " , SqlDbType.VarChar, 20 );
paramMyName.Direction = ParameterDirection.Output;
SqlParameter paramReturnValue = cmd.Parameters.Add( " Return " , SqlDbType.Int);
paramReturnValue.Direction = ParameterDirection.ReturnValue;
// cmd.Parameters.AddWithValue("@myAge", 34);
// 执行查询,获取存储过程执行后的输出参数和返回参数值
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
// 输出结果:MyName=xuminghui ReturnValue=2011
Console.WriteLine( " MyName={0}\t ReturnValue={1}\t \n " , paramMyName.Value, paramReturnValue.Value);
}
-- Author: xuminghui
-- Create date: 2011/03/28
-- Description: 带默认参数,输出参数和返回值的存储过程
-- =============================================
CREATE PROCEDURE ProcedureTest
@myName varchar( 20 ) output,
@myAge int = 33
AS
BEGIN
SET NOCOUNT ON;
set @myName='xuminghui'
return 1978+@myAge
END
GO
*/
private static void SqlCommandWithComplexParameters()
{
string connect = " Data Source=.\\sqlexpress; Initial Catalog=NorthWind ; Integrated Security = true; " ;
// 准备Connection对象和Command对象:设置Command对象调用存储过程
SqlConnection cn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand( " ProcedureTest " , cn);
cmd.CommandType = CommandType.StoredProcedure;
// 设定参数:输出参数-参数名称必须和存储过程输出参数名一致;
// 返回值参数-参数名称任意、修改默认参数
SqlParameter paramMyName = cmd.Parameters.Add( " @myname " , SqlDbType.VarChar, 20 );
paramMyName.Direction = ParameterDirection.Output;
SqlParameter paramReturnValue = cmd.Parameters.Add( " Return " , SqlDbType.Int);
paramReturnValue.Direction = ParameterDirection.ReturnValue;
// cmd.Parameters.AddWithValue("@myAge", 34);
// 执行查询,获取存储过程执行后的输出参数和返回参数值
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
// 输出结果:MyName=xuminghui ReturnValue=2011
Console.WriteLine( " MyName={0}\t ReturnValue={1}\t \n " , paramMyName.Value, paramReturnValue.Value);
}