存储过程的参数问题与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();

３．　带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, 1000));
                cmd.Parameters["@guamean"].Direction = ParameterDirection.Output;
                cmd.Parameters.Add(new SqlParameter("@guaname", SqlDbType.NVarChar, 10));
                cmd.Parameters["@guaname"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                c = cmd.Parameters["@guamean"].Value.ToString();
                d = cmd.Parameters["@guaname"].Value.ToString();