存储过程的参数问题与C#中的调用

1. 带参数的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_select_gua]
@num int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select mean from tb_gua where id=@num END

C#中的调用

                conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_select_gua";
cmd.Connection = conn;
cmd.Parameters.Add(new SqlParameter("@num", SqlDbType.Int));
cmd.Parameters["@num"].Value = Calculator();
c = (string)cmd.ExecuteScalar();

2. 带rerurn的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_select_gua2]
(@num1 int,
@num2 int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
return @num1+@num2; END

C#中的调用

                conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_select_gua2";
cmd.Connection = conn;
cmd.Parameters.Add(new SqlParameter("@num1", SqlDbType.Int));
cmd.Parameters["@num1"].Value = Calculator();
cmd.Parameters.Add(new SqlParameter("@num2", SqlDbType.Int));
cmd.Parameters["@num2"].Value = Calculator();
cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
c = cmd.Parameters["@return"].Value.ToString();

3. 带output参数的调用

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_select_gua1]
(@num int,
@guamean nvarchar(1000)='' output,
@guaname nvarchar(10)='' output)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select @guaname=name,@guamean=mean from tb_gua where id=@num END

C#中的调用

                conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_select_gua1";
cmd.Connection = conn;
cmd.Parameters.Add(new SqlParameter("@num", SqlDbType.Int));
cmd.Parameters["@num"].Value = Calculator();
cmd.Parameters.Add(new SqlParameter("@guamean", SqlDbType.NVarChar, ));
cmd.Parameters["@guamean"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@guaname", SqlDbType.NVarChar, ));
cmd.Parameters["@guaname"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
c = cmd.Parameters["@guamean"].Value.ToString();
d = cmd.Parameters["@guaname"].Value.ToString();
上一篇:ZooKeeper的安装与部署


下一篇:Linux虚拟机配置SSH免密登录