在存储过程中
creater proc proctest
as
select * form a
select * from b如何用TAdoStoredProc取得返回的多个数据集合(是动态个数数据集),我不想使用临时表,然后连接到QuickReport报表, 显示,躬请各位赐教!
creater proc proctest
as
select * form a
select * from b如何用TAdoStoredProc取得返回的多个数据集合(是动态个数数据集),我不想使用临时表,然后连接到QuickReport报表, 显示,躬请各位赐教!
as
select * form a left join
select * from b on ....
as
while ()
begin
select * from a
下次
select * from b
下次
end
@aVar int)
as
begin
if @aVar=1 then
select * from a
if @aVar=2 then
select * from b
.....
end
create proc proctest(
@aVar varchar (20))
as
begin
select * from @aVar
end
是我没有把我的意思表达明白,但是我的意思是create proc proctest
as
while (i < 10)
begin
if i = 1
select 1 col1, 2 col2, 3 col3 from a
if i = 2
select 4 col1, 5 col2, 6 col3 from a
if i = 3
select 7 col1, 8 col2, 9 col3 from a
if i = 4
end我取得的结果集是
col1, col2 col3
1, 2, 3, (结果集1)
4, 5, 6 (结果集2)
7, 8, 9 (结果集3)
.......
........我想在delphi中访问者个整个集合
create proc proctest(
@aVar int)
as
begin
if @aVar=1
select Field1 AS Coll1, Field2 as Coll2 from TableName
if @aVar=2
select Field3 AS Coll1, Field4 as Coll2 from TableName
.....
end
用SQL 不可以吗?存储过程概念上将不返回数据集,你这样的话,也只会是用另外一个DataSet;所以,我觉的用普通的DataSet可能更好一些:如:
with TQuery(DataSet) do
begin
if Active then
Active := False;
SQL.Clear;
SQL.Add('select * form a left join select * from b on ....');
Prepared;
Open;
end;
select @week Zc, @courseDate courseDate, @weekday Xq, @amCourse AmCourse, @pmCourse PmCourse, @courseTime CourseTime, @teacher Teacher 我如何用union合并,不行吧, 要在delphi中访问整个数据集存储过程如下:
create PROCEDURE ProcCourseQuery
(@PhaseId int, @termDate datetime)
AS
declare
@startDate Datetime, --开始时间
@endDate Datetime --结束时间
SELECT @startDate = Fd_Ksrq, @endDate = Fd_Jsrq FROM T_Jdjhb WHERE FI_JDJH_ID = @PhaseId
declare
@i int,
@span int,
@count int, --实际的天数
@week int,
@month int,
@day int,
@weekday varchar(50),
@amCourse varchar(100),
@pmCourse varchar(100),
@courseTime varchar(50),
@teacher varchar(50),
@date datetime,
@courseDate datetime
取循环次数啊
set @span = datediff(Day, @startDate, @endDate)
set @i = 0
while (@i <= @span)
begin
set @date = dateadd(day, @i, @startDate)
if (datepart(dw, @date) != 1 and datepart(dw, @date) != 7)
begin
--产生一行数据
set @week = datediff(week, @termDate, @date) + 1
--set @courseDate = convert(varchar(4), datepart(yy, @date)) + '-' + convert(varchar(2), datepart(mm, @date)) + '-' + convert(varchar(2), datepart(dd, @date))
set @courseDate = str(datepart(yy, @date)) + '-' + convert(varchar(2), datepart(mm, @date)) + '-' + convert(varchar(2), datepart(dd, @date))
set @weekday = right(DATENAME(dw, @date), 1)
--上午的课程
set @amCourse = ''
set @courseTime = ''
set @teacher = ''
--是否是有课
SELECT @amCourse = Fs_Kcmc, @courseTime = cast(Fi_Ks as varchar(10)), @teacher = Fs_Name FROM T_Kcszb a, T_Xxkkkc b, T_Teacher_Docu c WHERE a.Fi_Jdjh_Id = @PhaseId AND a.Fd_Skrq = @date AND a.Fi_Sksj = 590001 AND a.Fi_Course_Id = b.Fi_Course_Id and a.Fi_Zg_Id = c.Fi_Zg_Id
--是否有活动
if (@@rowcount != 1)
select @amCourse = Fs_Name_Small from T_Qtjxhdb a, T_Refe b where a.Fi_Hdlx = b.Fi_Key_Code and a.Fi_Jdjh_Id = @PhaseId and a.Fd_Hdrq = @date and a.Fi_Hdsj = 590001
--if (@amCourse = '')
--set @amCourse = '无'
--下午的课程
set @pmCourse = ''
--是否是有课
SELECT @pmCourse = Fs_Kcmc, @courseTime = cast(Fi_Ks as varchar(10)), @teacher = @teacher + ' ' + Fs_Name FROM T_Kcszb a, T_Xxkkkc b, T_Teacher_Docu c WHERE a.Fi_Jdjh_Id = @PhaseId AND a.Fd_Skrq = @date AND a.Fi_Sksj = 590002 AND a.Fi_Course_Id = b.Fi_Course_Id and a.Fi_Zg_Id = c.Fi_Zg_Id
--是否有活动
if (@@rowcount != 1)
select @pmCourse = Fs_Name_Small from T_Qtjxhdb a, T_Refe b where a.Fi_Hdlx = b.Fi_Key_Code and a.Fi_Jdjh_Id = @PhaseId and a.Fd_Hdrq = @date and a.Fi_Hdsj = 590002
--取该行数据
select @week Zc, @courseDate courseDate, @weekday Xq, @amCourse AmCourse, @pmCourse PmCourse, @courseTime CourseTime, @teacher Teacher
set @date = @date + 1
if (@i + 1 <= @span and datepart(dw, @date) != 1 and datepart(dw, @date) != 7)
begin
set @i = @i
end
end
set @i = @i + 1
end