一个完整的存储过程 returnValue

本例为插入一条用户数据并把新数据的编号返回
*注意:据说返回参数必须是sqlcommand 的第一个参数

        存储过程为:
                     CREATE PROCEDURE InsertUser
(
@user_id nvarchar(16),
@password nvarchar(20),
@user_name nvarchar(30),
@sex nvarchar(10),
@city nvarchar(50),
@address nvarchar(255),
@tel nvarchar(20),
@mobilephone nvarchar(20),
@email nvarchar(100),
@postcode nvarchar(20),
@register_date datetime,  
@member_type nvarchar(20)
)
as
Insert into usermanage([user_id],password,[user_name],sex,city,address,tel,mobilephone,email,postcode,register_date,member_type)
   values(@user_id,@password,@user_name,@sex,@city,@address,@tel,@mobilephone,@email,@postcode,@register_date,@member_type
    )
declare @returnValue int
select @returnValue=@@IDENTITY
return @returnValue
GO

简单表示为

                   create pro 名称
                            (
                                @u_id int
                                    .........
                           
                            )
                                          as
                                          insert into() values()
                 declare @returnValue int
                 select @returnValue=@@identity
                 return @returnValue
存储过程完毕
------------------------------------------------------------------------------------------------------------
下边是c#代码
        public int AddUserSimpleInfo(string user_id, string password, string user_name, string sex, string city, string address, string tel, string mobilephone, string email, string postcode)
        {
            using (SqlConnection conn = new SqlConnection(new NewData().ConnString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "InsertUser";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter returnValue = new SqlParameter("@returnValue", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(returnValue);
                cmd.Parameters.Add("@user_id", SqlDbType.NVarChar).Value = user_id;
                cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
                cmd.Parameters.Add("@user_name", SqlDbType.NVarChar).Value = user_name;
                cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = sex;
                cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
                cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = address;
                cmd.Parameters.Add("@tel", SqlDbType.NVarChar).Value = tel;
                cmd.Parameters.Add("@mobilephone", SqlDbType.NVarChar).Value = mobilephone;
                cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
                cmd.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postcode;
                cmd.Parameters.Add("@register_date", SqlDbType.DateTime).Value = DateTime.Today;
                cmd.Parameters.Add("@member_type", SqlDbType.NVarChar).Value = "普通会员";
               
                try
                {
                    cmd.ExecuteNonQuery();
                    return Convert.ToInt32(returnValue.Value.ToString());
                }
                catch (SqlException ex)
                {
                    return 0;
                }
                finally
                {
                    conn.Close();
                }
            }
        }