下面的存储过程 如何在dal层里写 调用 返回多个值呀?CREATE PROCEDURE p_mailzg
@ryxm varchar(50),
@djry varchar(30),
@djh varchar(30),
@djlx varchar(50) AS
begin
declare @sjry varchar(50)
declare @sjmail varchar(50)
declare @sjzw varchar(50)
declare @s varchar(10)
declare @gh varchar(50)select @sjry=fry,@gh=fryid from hr_qhb where (ryxm=@ryxm) and (djlx=@djlx)
select @sjmail=mail,@sjzw=zw from xzhr where gh=@ghif @sjry <> '0'
begin
set @s='1'
select @sjry,@sjmail,@sjzw,@s
endif @sjry='0' and @djry<>'XXX'
begin
set @s='2'
select @sjry,@sjmail,@sjzw,@s
--select xname,mail,zw from maillist where js='w' and djh=@djh
endif @sjry='0' and @djry='XXX'
begin
select @sjry=ryxm,@sjmail=mail,@sjzw=zw from xzhr where gh='1001'
set @s='1'
select @sjry,@sjmail,@sjzw,@s
endreturn
end
GO
下面是返回单个值的 没有问题,那向上面的存储过程返回多个值的怎么写呢?
public static string jsdate(string d1, string d2)
{
SQLHelper sqlhelp = new SQLHelper();
SqlParameter[] par = { sqlhelp.CreateOutParam("@s3",SqlDbType.Int,4),
sqlhelp.CreateInParam("@sta", SqlDbType.DateTime,8,d1),
sqlhelp.CreateInParam("@end",SqlDbType.DateTime,8,d2)}; try
{
sqlhelp.RunProc("p_js", par);
return Convert.ToString(par[0].Value);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
@ryxm varchar(50),
@djry varchar(30),
@djh varchar(30),
@djlx varchar(50) AS
begin
declare @sjry varchar(50)
declare @sjmail varchar(50)
declare @sjzw varchar(50)
declare @s varchar(10)
declare @gh varchar(50)select @sjry=fry,@gh=fryid from hr_qhb where (ryxm=@ryxm) and (djlx=@djlx)
select @sjmail=mail,@sjzw=zw from xzhr where gh=@ghif @sjry <> '0'
begin
set @s='1'
select @sjry,@sjmail,@sjzw,@s
endif @sjry='0' and @djry<>'XXX'
begin
set @s='2'
select @sjry,@sjmail,@sjzw,@s
--select xname,mail,zw from maillist where js='w' and djh=@djh
endif @sjry='0' and @djry='XXX'
begin
select @sjry=ryxm,@sjmail=mail,@sjzw=zw from xzhr where gh='1001'
set @s='1'
select @sjry,@sjmail,@sjzw,@s
endreturn
end
GO
下面是返回单个值的 没有问题,那向上面的存储过程返回多个值的怎么写呢?
public static string jsdate(string d1, string d2)
{
SQLHelper sqlhelp = new SQLHelper();
SqlParameter[] par = { sqlhelp.CreateOutParam("@s3",SqlDbType.Int,4),
sqlhelp.CreateInParam("@sta", SqlDbType.DateTime,8,d1),
sqlhelp.CreateInParam("@end",SqlDbType.DateTime,8,d2)}; try
{
sqlhelp.RunProc("p_js", par);
return Convert.ToString(par[0].Value);
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
欢迎使用CSDN 小秘书
每天顶贴即可得到10个积分O(∩_∩)O~
***********************************************************[/align]
存储过程里面加output
2)通过select语句返回结果集
http://topic.csdn.net/u/20091204/21/722689e1-7824-497c-b709-4b1118264633.html
set QUOTED_IDENTIFIER ON
go 这个是我写的简单存储过程、输入id返回两个字段的内容
ALTER proc [dbo].[CHAabout]
@id int,
@about nvarchar(3900) output,
@Eabout varchar (5000) output
as
select @about=about,@Eabout=Eabout from About where id=@id 这时是执行函数 public bool ALLabout(int id, out string about, out string Eabout)//查询公司信息
{ SqlConnection cn = new SqlConnection(Common.Class1.con);
SqlCommand Cmd = new SqlCommand(Common.Class1.Chbout, cn);//存储过程写在另外一个类中。这里有Common.clasq.Chabout调用存储过程名称
Cmd.Parameters.Add(new SqlParameter("@about", SqlDbType.NVarChar, 3900));
Cmd.Parameters["@about"].Direction = ParameterDirection.Output;
Cmd.Parameters.Add(new SqlParameter("@Eabout", SqlDbType.VarChar, 5000));
Cmd.Parameters["@Eabout"].Direction = ParameterDirection.Output;
Cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
Cmd.Parameters["@id"].Value = id;
Cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
Cmd.ExecuteNonQuery();
about = Cmd.Parameters["@about"].Value.ToString();//返回值
Eabout = Cmd.Parameters["@Eabout"].Value.ToString();//返回值
cn.Close();
return true;
}页面中调用:
int id = 1;//输入参数。
string about = "";// 全局变量
string Eabout = "";// 全局变量
dac.ALLabout(id, out about, out Eabout);
Abouta.Text = about.ToString();//这里就是最终效果了
Lable1.Text=Eabout.ToString(();//这里就是最终效果了
自己试一下吧
{
SQLHelper sqlhelp = new SQLHelper();
SqlParameter[] par = { sqlhelp.CreateOutParam("@s3",SqlDbType.Int,4),
sqlhelp.CreateInParam("@sta", SqlDbType.DateTime,8,d1),
sqlhelp.CreateInParam("@end",SqlDbType.DateTime,8,d2)}; try
{
sqlhelp.RunProc("p_js", par);
string[] str=new string[par.count];
for(int i=0;i<par.count;i++){
str[i]=Convert.ToString(par[i].Value);
}
return str;
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
如果单是多个值设置output参数可以实现多条记录的话可在存储过程建立个临时table 将查询到的每条数据插入table 最后返回该table
(
UserID varchar(10)
)
insert @newtable select UserID from Playernfo