CREATE Function CB_F_GetVmCard
(
@StartTime nvarchar(20),
@EndTime nvarchar(20),
@ALogRelation nvarchar(10),
@BLogRelation nvarchar(10),
@TotalCard int,
@CardNo nvarchar(20),
@SingleCard int
)
returns table
asif @ALogRelation=''
begin
return
(
SELECT CB_ORDER.ORDER_NO, SUM(CB_ORDERDETAIL.PRODQUANTITY)
AS SUMQUANTITY
FROM CB_ORDER INNER JOIN
CB_ORDERDETAIL ON CB_ORDER.ORDERID = CB_ORDERDETAIL.ORDERID
WHERE (CB_ORDER.RETTIME IS NULL) AND (CB_ORDERDETAIL.DELFLAG = 0) AND
(CB_ORDER.SENDDATE>=@StartTime AND CB_ORDER.SENDDATE<=@EndTime) AND
(CB_ORDER.TOTALCOST > 0) AND (LEFT(CB_ORDERDETAIL.PRODUCTID, 1)
<> 'i') AND (CB_ORDER.STATE = 2) AND
(CB_ORDERDETAIL.PRODTYPE = N'card') AND (CB_ORDERDETAIL.PRODPRICE > 0)
GROUP BY CB_ORDER.ORDER_NO
)
end
這個函數存在什么問題?請大家賜教!
(
@StartTime nvarchar(20),
@EndTime nvarchar(20),
@ALogRelation nvarchar(10),
@BLogRelation nvarchar(10),
@TotalCard int,
@CardNo nvarchar(20),
@SingleCard int
)
returns table
asif @ALogRelation=''
begin
return
(
SELECT CB_ORDER.ORDER_NO, SUM(CB_ORDERDETAIL.PRODQUANTITY)
AS SUMQUANTITY
FROM CB_ORDER INNER JOIN
CB_ORDERDETAIL ON CB_ORDER.ORDERID = CB_ORDERDETAIL.ORDERID
WHERE (CB_ORDER.RETTIME IS NULL) AND (CB_ORDERDETAIL.DELFLAG = 0) AND
(CB_ORDER.SENDDATE>=@StartTime AND CB_ORDER.SENDDATE<=@EndTime) AND
(CB_ORDER.TOTALCOST > 0) AND (LEFT(CB_ORDERDETAIL.PRODUCTID, 1)
<> 'i') AND (CB_ORDER.STATE = 2) AND
(CB_ORDERDETAIL.PRODTYPE = N'card') AND (CB_ORDERDETAIL.PRODPRICE > 0)
GROUP BY CB_ORDER.ORDER_NO
)
end
這個函數存在什么問題?請大家賜教!
(
@StartTime nvarchar(20),
@EndTime nvarchar(20),
@ALogRelation nvarchar(10),
@BLogRelation nvarchar(10),
@TotalCard int,
@CardNo nvarchar(20),
@SingleCard int
)
returns @r table (
ORDER_NO nvarchar(20),
SUMQUANTITY int
)
as
begin
if @ALogRelation=''
begin
insert @r
SELECT CB_ORDER.ORDER_NO, SUM(CB_ORDERDETAIL.PRODQUANTITY)
AS SUMQUANTITY
FROM CB_ORDER INNER JOIN
CB_ORDERDETAIL ON CB_ORDER.ORDERID = CB_ORDERDETAIL.ORDERID
WHERE (CB_ORDER.RETTIME IS NULL) AND (CB_ORDERDETAIL.DELFLAG = 0) AND
(CB_ORDER.SENDDATE>=@StartTime AND CB_ORDER.SENDDATE <=@EndTime) AND
(CB_ORDER.TOTALCOST > 0) AND (LEFT(CB_ORDERDETAIL.PRODUCTID, 1)
<> 'i') AND (CB_ORDER.STATE = 2) AND
(CB_ORDERDETAIL.PRODTYPE = N'card') AND (CB_ORDERDETAIL.PRODPRICE > 0)
GROUP BY CB_ORDER.ORDER_NO
end
else
begin
...
end return
endgo