CREATE PROCEDURE [dbo].[qrFindAnUserAllValidProgramProductAc2UserNo_2]
@UserNo char(16)
AS
SELECT d.[productID], MIN(StartDateNow) as StartDate, MAX(endDateNow) as EndDate, COUNT(*) as cntBills
FROM [WFDN3].[dbo].[wfBillDetail] AS d
WHERE billID=ANY
(
SELECT billID FROM [WFDN3].[dbo].[wfBill]
WHERE
(
userNo=@UserNo
AND ( IncludeProgram = '1' )
) )
aND
(
d.[productID] = ANY
(
SELECT e.[productID] FROM wfProduct as e WHERE e.[CategoryID]=
(
SELECT f.[CategoryID] FROM wfProductCategories as f
WHERE f.[CategoryName] = '节目'
)
)
)
/*ORDER BY d.[productID] ASC*/
GROUP BY d.[productID]如上存储过程,
我想自己建立个函数,声明个变量,然后把如上存储过程返回表中的一个字段 EndDate赋给变量。请大家指教,分不够还可以加!
不考虑EndDate有多行的问题
@UserNo char(16)
AS
SELECT d.[productID], MIN(StartDateNow) as StartDate, MAX(endDateNow) as EndDate, COUNT(*) as cntBills
FROM [WFDN3].[dbo].[wfBillDetail] AS d
WHERE billID=ANY
(
SELECT billID FROM [WFDN3].[dbo].[wfBill]
WHERE
(
userNo=@UserNo
AND ( IncludeProgram = '1' )
) )
aND
(
d.[productID] = ANY
(
SELECT e.[productID] FROM wfProduct as e WHERE e.[CategoryID]=
(
SELECT f.[CategoryID] FROM wfProductCategories as f
WHERE f.[CategoryName] = '节目'
)
)
)
/*ORDER BY d.[productID] ASC*/
GROUP BY d.[productID]如上存储过程,
我想自己建立个函数,声明个变量,然后把如上存储过程返回表中的一个字段 EndDate赋给变量。请大家指教,分不够还可以加!
不考虑EndDate有多行的问题
解决方案 »
- CBT钩子失效问题。
- 我想问一下,帮外面的公司做一个功能多少钱啊
- 老菜的问题了 WIN2003中Delphi 7中的Project菜单中Options菜单打不开
- 关于combobox,一个简单的问题,
- 如何停止一个word与ppt的进程,各位大哥代码提示一下
- 如何得到规定一个日期的前一天,后一天日期?
- 如何在dbgrid中置类似于DateTimePicker的控件
- 散分!回答都有分。
- 怎样用Delphi编写视频捕捉程序,能解决问题的一定给分
- 有关delphi与串口通信
- Windows7 终于要来了,我的老奔不会再也奔不动了(P4 3.0E HT+1G DDR400)
- 200分求一通用导出Excel的存储过程
UserNO : String;begin
userNo := '123';
qry.close;
qry.sql.text := ' declare @No char(6) Execute qrFindAnUserAllValidProgramProductAc2UserNo_2 '+Quotedstr(UserNo);
qry.open;
showMessage(qry.fields[0].asstring);
end;
AS
-- 不考慮多行就這樣寫
SELECT d.[productID], MIN(StartDateNow) as StartDate,@EndDate = MAX(endDateNow) as EndDate, COUNT(*) as cntBills
FROM [WFDN3].[dbo].[wfBillDetail] AS d
WHERE billID=ANY
(
SELECT billID FROM [WFDN3].[dbo].[wfBill]
WHERE
(
userNo=@UserNo
AND ( IncludeProgram = '1' )
) )
aND
(
d.[productID] = ANY
(
SELECT e.[productID] FROM wfProduct as e WHERE e.[CategoryID]=
(
SELECT f.[CategoryID] FROM wfProductCategories as f
WHERE f.[CategoryName] = '节目'
)
)
)
/*ORDER BY d.[productID] ASC*/
GROUP BY d.[productID]
var
UserNO : String;begin
userNo := '123';
qry.close;
qry.sql.text := ' declare @EndDate Datetime Execute qrFindAnUserAllValidProgramProductAc2UserNo_2 '+Quotedstr(UserNo)+' @EndDate Output select @EndDate as EndDate';
qry.open;
showMessage(qry.fieldbyname('EndDate').asstring);
end;
with DM.adoq_Chart do
begin
Close;
SQL.Clear;
SQL.Add('EXEC ShopFloor_OrderStatus '+quotedstr(Plato_Frm.RzEdit4.Text)+','+quotedstr('All'));
ExecSQL;
end;
function GetEndDate(UserNo: string; var EndDate: TDateTime): Boolean;
begin
with ADOStoredProc1 do
begin
Close;
ProcedureName := 'qrFindAnUserAllValidProgramProductAc2UserNo_2'; //你这个名字实在是太长了一点
Parameters.Refresh;
Parameters.ParamByName('@UserNo').Value := UserNo;
Open;
if not IsEmpty then
begin
EndDate := FieldByName('EndDate').AsDateTime;
Result := true;
end
else Result := false;
end;
end;
CREATE FUNCTION Func_EndDate (@i datetime)
RETURNS datetime AS
BEGIN
declare @j datetime //声明的变量
set @j = @i
return @j
END存储过程里加入dbo.Func_EndDate(EndDate)
不知道你这样做有什么目的!~
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[qrFindAnUserAllValidProgramProductAc2UserNo_2](@UserNo char(16))
RETURNS datetime
AS
BEGIN
declare @enddate datetime
SELECT d.[productID], MIN(StartDateNow) as StartDate, MAX(endDateNow)as EndDate, COUNT(*) as cntBills
into temp
FROM [WFDN3].[dbo].[wfBillDetail] AS d
WHERE billID=ANY
(
SELECT billID FROM [WFDN3].[dbo].[wfBill]
WHERE
(
userNo=@UserNo
AND ( IncludeProgram = '1' )
) )
aND
(
d.[productID] = ANY
(
SELECT e.[productID] FROM wfProduct as e WHERE e.[CategoryID]=
(
SELECT f.[CategoryID] FROM wfProductCategories as f
WHERE f.[CategoryName] = '节目'
)
)
)
/*ORDER BY d.[productID] ASC*/
GROUP BY d.[productID]
select @enddate=EndDate from temp
drop table temp
return @enddate
END
select @enddate=EndDate from temp後加上條件 where
可以取出EndDate,這裡,我是把你的EndDate當作時間類型的。