一.SqlServer 中
1. 创建表
create
table
testuser(
id
int, --primary key,
names varchar(50),
address varchar(50),
paw varchar(50)
)
2.创建存储过程
create
proc testp12(@idint ,@namesvarchar(50),@addressvarchar(50),@pawvarchar(50))
asbegindeclare@maxidintif@idisnullor@id<5beginreturn-1;
end--set IDENTITY_INSERT testuser oninsertinto testuser(id,names,address,paw)values(@id,@names,@address,@paw)
--set IDENTITY_INSERT testuser offselect@maxid=(selectmax(id) from testuser)
return@maxidend
3.c#对应的代码处理
#region"SqlServer中存储过程Return返回值处理"
/*
public void getresult(int id, string names, string address, string pwd)
{
string str = " server=192.168.xxx;user id=xx;password=xxxxxxxx;database=xxxxx_xx;min pool size=4;max pool size=4;packet size=3072";
SqlConnection conn = new SqlConnection(str);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "testp12";
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
cmd.Parameters["@id"].Value = id;
cmd.Parameters.Add(new SqlParameter("@names", SqlDbType.VarChar, 50));
cmd.Parameters["@names"].Value = names;
cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));
cmd.Parameters["@address"].Value = address;
cmd.Parameters.Add(new SqlParameter("@paw", SqlDbType.VarChar, 50));
cmd.Parameters["@paw"].Value = pwd;
cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
Response.Write(cmd.Parameters["@return"].Value.ToString());
Response.Write("<br/>");
Response.Write("hello world");
}*/#endregion
二.MySQL中
1.创建表
create
table
testuser(
id
int, names varchar(50),
address varchar(50),
paw varchar(50)
)
2.创建函数
create
FUNCTION testp12(_id int , _names varchar(50), _address varchar(50), _paw varchar(50) )
RETURNSINT
label_pro: BEGINdeclare _maxid int;
declare returnid int;
if _id isnullor _id<5thenbeginset returnid=-1;
RETURN returnid;
leave label_pro;
end ;
endif;
-- set IDENTITY_INSERT testuser oninsertinto testuser(id,`names`,address,paw)values(_id,_names,_address,_paw);
-- set IDENTITY_INSERT testuser offset _maxid=(selectmax(id) from testuser);
set returnid=_maxid;
RETURN returnid;
end
3.C#代码实现----函数实现
#region"函数实现SqlServer中存储过程功能Return"
///<summary>/// 函数实现SqlServer中存储过程功能
///</summary>///<param name="id"></param>///<param name="names"></param>///<param name="address"></param>///<param name="pwd"></param>publicvoid getresult(int id, string names, string address, string pwd)
{
string str = "Server=192.168.xxxx;Database=xxxx_xxx; port=xxxx;user id=xxxxx;password= providerName=MySql.Data.MySqlClient";
MySqlConnection conn = new MySqlConnection(str);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "fnetravel2_db.testp12";
cmd.Parameters.Add(new MySqlParameter("_id", MySqlDbType.Int32));
cmd.Parameters["_id"].Value = id;
cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50));
cmd.Parameters["_names"].Value = names;
cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50));
cmd.Parameters["_address"].Value = address;
cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50));
cmd.Parameters["_paw"].Value = pwd;
// cmd.Parameters.Add(new MySqlParameter("returnid", MySqlDbType.Int32));
//cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("returnid", MySqlDbType.Int32, 11);
//设置参数的类型为输出参数,默认情况下是输入,
cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
string name = cmd.Parameters["returnid"].Value.ToString();
conn.Close();
Response.Write(name);
Response.Write("<br/>");
Response.Write("hello world");
}
#endregion
4.存储过程实现
create
PROCEDURE testp122(in _id int ,in _names varchar(50),in _address varchar(50),in _paw varchar(50),out returnid int )
label_pro:BEGINdeclare _maxid int;
-- declare returnid int;if _id isnullor _id<5thenbeginset returnid=-1;
leave label_pro;
end ;
endif;
-- set IDENTITY_INSERT testuser oninsertinto testuser(id,`names`,address,paw)values(_id,_names,_address,_paw);
-- set IDENTITY_INSERT testuser offset _maxid=(selectmax(id) from testuser);
set returnid=_maxid;
end
5.C#代码实现----存储过程
#region"mysql存储过程实现SqlServer中存储过程功能Return"
///<summary>//////</summary>///<param name="id"></param>///<param name="names"></param>///<param name="address"></param>///<param name="pwd"></param>publicvoid getresult(int id, string names, string address, string pwd)
{
string str = "Server=192.168.xxxxxx;Database=xxxxx_dxxxxxxb; port=xxxxx;user id=xxxxx;password= providerName=MySql.Data.MySqlClient";
MySqlConnection conn = new MySqlConnection(str);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "fnetravel2_db.testp122";
cmd.Parameters.Add(new MySqlParameter("_id", MySqlDbType.Int32));
cmd.Parameters["_id"].Value = id;
cmd.Parameters.Add(new MySqlParameter("_names", MySqlDbType.VarChar, 50));
cmd.Parameters["_names"].Value = names;
cmd.Parameters.Add(new MySqlParameter("_address", MySqlDbType.VarChar, 50));
cmd.Parameters["_address"].Value = address;
cmd.Parameters.Add(new MySqlParameter("_paw", MySqlDbType.VarChar, 50));
cmd.Parameters["_paw"].Value = pwd;
// cmd.Parameters.Add(new MySqlParameter("returnid", MySqlDbType.Int32));
//cmd.Parameters["returnid"].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("returnid", MySqlDbType.Int32, 11);
//设置参数的类型为输出参数,默认情况下是输入,
cmd.Parameters["returnid"].Direction = ParameterDirection.Output;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
string name = cmd.Parameters["returnid"].Value.ToString();
conn.Close();
Response.Write(name);
Response.Write("<br/>");
Response.Write("hello world");
}
}
#endregion
看到上面想到是不是SqlServer中也可以实现这个功能，但是测试发现SqlServer中函数不允许insert语句。但是如果函数返回表则是可以的。如这个函数：
CREATE
FUNCTION func(@selectionint)
RETURNS@tableTABLE
(
id char(4) primarykeynotnull,
names nvarchar(4) null
)
ASBEGINIF@selection=0INSERTINTO@tableSELECT id,names FROM testuser
ELSEINSERTINTO@tableSELECT id,names FROM testuser
ReturnEND
这些例子都是我实践得到了。
记录一点点，收货一点点。
原文：http://www.cnblogs.com/annabook/p/3835456.html
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！