下面这个存储过程是实现将表t_jxjh_ydjxrws中满足条件的数据添加到表t_jxrws中
CREATE
PROCEDURE
jxjh_sczxjxrws
@xnxqbh varchar ( 5 ),
@zybh varchar ( 10 ),
@ccbh varchar ( 10 ),
@nj int
AS
declare @li_bjs int
declare @ls_kc varchar ( 100 )
declare @li_zks int
declare cur_1 cursor for select bjs,kc,zks from t_jxjh_ydjxrws where xnxqbh = @xnxqbh and zybh = @zybh and ccbh = @ccbh and nj = @nj -- 定义游标
open cur_1 -- 打开游标l
fetch next from cur_1 into @li_bjs , @ls_kc , @li_zks -- 取第1条记录
while @@fetch_status = 0 -- 循环读取剩余记录
begin
insert into t_jxrws(xnxqbh,zybh,ccbh,nj,bjs,kc,zks) values ( @xnxqbh , @zybh , @ccbh , @nj , @li_bjs , @ls_kc , @li_zks )
fetch next from cur_1 into @li_bjs , @ls_kc , @li_zks
end
GO
@xnxqbh varchar ( 5 ),
@zybh varchar ( 10 ),
@ccbh varchar ( 10 ),
@nj int
AS
declare @li_bjs int
declare @ls_kc varchar ( 100 )
declare @li_zks int
declare cur_1 cursor for select bjs,kc,zks from t_jxjh_ydjxrws where xnxqbh = @xnxqbh and zybh = @zybh and ccbh = @ccbh and nj = @nj -- 定义游标
open cur_1 -- 打开游标l
fetch next from cur_1 into @li_bjs , @ls_kc , @li_zks -- 取第1条记录
while @@fetch_status = 0 -- 循环读取剩余记录
begin
insert into t_jxrws(xnxqbh,zybh,ccbh,nj,bjs,kc,zks) values ( @xnxqbh , @zybh , @ccbh , @nj , @li_bjs , @ls_kc , @li_zks )
fetch next from cur_1 into @li_bjs , @ls_kc , @li_zks
end
GO
CREATE PROCEDURE jxjh_sczxjxrws
@xnxqbh varchar(5),
@zybh varchar(10),
@ccbh varchar(10),
@nj int
AS
AS之前的这段用于声明调用存储过程时需要传递的参数!每个参数后面必须用逗号分隔。
AS
declare @li_bjs int
declare @ls_kc varchar(100)
declare @li_zks int
AS之后的这段的变量声明用于在程序中使用!
在asp.net页面中调用存储过程(这里是在类中实现的)
public
void
jxjh_sczxjxrws(
string
xnxqbh,
string
zybh,
string
ccbh,
int
nj)
... {
jxjh get_conn = new jxjh();
SqlConnection myconn = get_conn.get_conn(); //
SqlCommand mycomm = new SqlCommand("jxjh_sczxjxrws", myconn);
mycomm.CommandType = CommandType.StoredProcedure;
//为存储过程增加参数
mycomm.Parameters.Add("@xnxqbh", SqlDbType.VarChar,5);
mycomm.Parameters.Add("@zybh", SqlDbType.VarChar,10);
mycomm.Parameters.Add("@ccbh",SqlDbType.VarChar,10);
mycomm.Parameters.Add("@nj", SqlDbType.Int);
mycomm.Parameters["@xnxqbh"].Value = xnxqbh;
mycomm.Parameters["@zybh"].Value = zybh;
mycomm.Parameters["@ccbh"].Value = ccbh;
mycomm.Parameters["@nj"].Value = nj;
//打开数据库连接,执行数据库访问命令,并关闭数据库连接
using (myconn)
...{
myconn.Open();
mycomm.ExecuteNonQuery();
mycomm.Dispose();
}
}
... {
jxjh get_conn = new jxjh();
SqlConnection myconn = get_conn.get_conn(); //
SqlCommand mycomm = new SqlCommand("jxjh_sczxjxrws", myconn);
mycomm.CommandType = CommandType.StoredProcedure;
//为存储过程增加参数
mycomm.Parameters.Add("@xnxqbh", SqlDbType.VarChar,5);
mycomm.Parameters.Add("@zybh", SqlDbType.VarChar,10);
mycomm.Parameters.Add("@ccbh",SqlDbType.VarChar,10);
mycomm.Parameters.Add("@nj", SqlDbType.Int);
mycomm.Parameters["@xnxqbh"].Value = xnxqbh;
mycomm.Parameters["@zybh"].Value = zybh;
mycomm.Parameters["@ccbh"].Value = ccbh;
mycomm.Parameters["@nj"].Value = nj;
//打开数据库连接,执行数据库访问命令,并关闭数据库连接
using (myconn)
...{
myconn.Open();
mycomm.ExecuteNonQuery();
mycomm.Dispose();
}
}