CREATE FUNCTION GetAFieldSum (@AField varchar(255))
RETURNS int
AS
BEGIN
DECLARE @Result int SELECT @Result=(
CASE @AField
WHEN 'COLUMN_NAME1' THEN SUM(COLUMN_NAME1)
WHEN 'COLUMN_NAME2' THEN SUM(COLUMN_NAME2)
ELSE SUM(COLUMN_NAME3)
)
FROM TABLE_NAME RETURN(@Result)
ENDDECLARE @Result int
EXEC @Result=GetAFieldSum('COLUMN_NAME1')同一个问题拿你这么多分可真不好意思.在贴子http://www.csdn.net/expert/topic/343/343373.shtm中,你说"在Function中使用Exec只可以执行扩展存储过程"这是不对了,EXEC除了可以执行存储过程外,主要一个功能就是实现动态SQL,比如根据不同的参数,返回不同的结果集.如/******************************************************************************************************
*过程名:sp_GetVoucherList
*机能: 返回收衣取衣凭证记录
*******************************************************************************************************/
CREATE PROCEDURE sp_GetVoucherList
@VoucherID varchar(15), --单据号码
@ClientTypeID varchar(10),
@ClientID varchar(10),
@BeginDate varchar(10),
@EndDate varchar(10),
@ServiceTypeID varchar(10),
@ServiceID varchar(10),
@SettleFlag varchar(1),
@TakeFlag varchar(1)
AS
DECLARE @strSQL varchar(8000)
SET @strSQL ='
SELECT
V.VoucherID "单据号码 ",
VD.ID "明细ID",
V.SettleFlag "结清 ",
CONVERT(varchar(10), V.ReceiveDate, 111) "收衣日期 ",
CT.TypeName "客户类型 ",
C.ClientName "客户 ",
ST.TypeName "类型 ",
S.Name "项目 ",
VD.StatusFlag "取衣 ",
B.BrandName "品牌 ",
CL.ColorName "颜色 ",
CONVERT(varchar(16), VD.Price, 1) "单价 ",
CONVERT(varchar(16), VD.Quantity, 1) "数量 ",
CONVERT(varchar(16), VD.TotalMoney, 1) "金额 ",
CONVERT(varchar(16), VD.Discount, 1) "折扣率 ",
CONVERT(varchar(16), VD.ARMoney, 1) "应收金额 ",
CONVERT(varchar(10), VD.PlanTakeDate, 111) "预取日期 "
FROM t_Voucher V
LEFT OUTER JOIN t_ClientType CT
ON V.ClientTypeID = CT.TypeID
LEFT OUTER JOIN t_Client C
ON V.ClientTypeID = C.ClientTypeID AND V.ClientID = C.ClientID,
t_Voucher_Detail VD
LEFT OUTER JOIN t_ServiceType ST
ON VD.ServiceTypeID = ST.TypeID
LEFT OUTER JOIN t_Service S
ON VD.ServiceID = S.ID
LEFT OUTER JOIN t_Brand B
ON VD.BrandID = B.ID
LEFT OUTER JOIN t_Color CL
ON VD.ColorID = CL.ID
WHERE
V.VoucherID= VD.VoucherID
AND V.StatusFlag = 0 ' IF @VoucherID<>''
SET @strSQL = @strSQL +' AND V.VoucherID = ''' + @VoucherID +''''
IF @ClientTypeID <> ''
SET @strSQL = @strSQL +' AND V.ClientTypeID = ''' + @ClientTypeID +''''
IF @ClientID <> ''
SET @strSQL = @strSQL +' AND V.ClientID = ''' + @ClientID +''''
IF @BeginDate <> ''
SET @strSQL = @strSQL +' AND CONVERT(varchar(10),V.ReceiveDate,111) >= ''' + @BeginDate +''''
IF @EndDate <> ''
SET @strSQL = @strSQL +' AND CONVERT(varchar(10),V.ReceiveDate,111) <= ''' + @EndDate +''''
IF @ServiceTypeID <> ''
SET @strSQL = @strSQL +' AND VD.ServiceTypeID = ''' + @ServiceTypeID +''''
IF @ServiceID <> ''
SET @strSQL = @strSQL +' AND VD.ServiceID = ''' + @ServiceID +''''
IF @SettleFlag <> ''
SET @strSQL = @strSQL +' AND V.SettleFlag = ' + @SettleFlag
IF @TakeFlag <> ''
SET @strSQL = @strSQL +' AND VD.StatusFlag = ' + @TakeFlag
SET @strSQL = @strSQL + ' ORDER BY V.VoucherID, V.ReceiveDate'
EXEC(@strSQL)
GO
/**************************************/
/* 学无止境 */
/**************************************/
RETURNS int
AS
BEGIN
DECLARE @Result int SELECT @Result=(
CASE @AField
WHEN 'COLUMN_NAME1' THEN SUM(COLUMN_NAME1)
WHEN 'COLUMN_NAME2' THEN SUM(COLUMN_NAME2)
ELSE SUM(COLUMN_NAME3)
)
FROM TABLE_NAME RETURN(@Result)
ENDDECLARE @Result int
EXEC @Result=GetAFieldSum('COLUMN_NAME1')同一个问题拿你这么多分可真不好意思.在贴子http://www.csdn.net/expert/topic/343/343373.shtm中,你说"在Function中使用Exec只可以执行扩展存储过程"这是不对了,EXEC除了可以执行存储过程外,主要一个功能就是实现动态SQL,比如根据不同的参数,返回不同的结果集.如/******************************************************************************************************
*过程名:sp_GetVoucherList
*机能: 返回收衣取衣凭证记录
*******************************************************************************************************/
CREATE PROCEDURE sp_GetVoucherList
@VoucherID varchar(15), --单据号码
@ClientTypeID varchar(10),
@ClientID varchar(10),
@BeginDate varchar(10),
@EndDate varchar(10),
@ServiceTypeID varchar(10),
@ServiceID varchar(10),
@SettleFlag varchar(1),
@TakeFlag varchar(1)
AS
DECLARE @strSQL varchar(8000)
SET @strSQL ='
SELECT
V.VoucherID "单据号码 ",
VD.ID "明细ID",
V.SettleFlag "结清 ",
CONVERT(varchar(10), V.ReceiveDate, 111) "收衣日期 ",
CT.TypeName "客户类型 ",
C.ClientName "客户 ",
ST.TypeName "类型 ",
S.Name "项目 ",
VD.StatusFlag "取衣 ",
B.BrandName "品牌 ",
CL.ColorName "颜色 ",
CONVERT(varchar(16), VD.Price, 1) "单价 ",
CONVERT(varchar(16), VD.Quantity, 1) "数量 ",
CONVERT(varchar(16), VD.TotalMoney, 1) "金额 ",
CONVERT(varchar(16), VD.Discount, 1) "折扣率 ",
CONVERT(varchar(16), VD.ARMoney, 1) "应收金额 ",
CONVERT(varchar(10), VD.PlanTakeDate, 111) "预取日期 "
FROM t_Voucher V
LEFT OUTER JOIN t_ClientType CT
ON V.ClientTypeID = CT.TypeID
LEFT OUTER JOIN t_Client C
ON V.ClientTypeID = C.ClientTypeID AND V.ClientID = C.ClientID,
t_Voucher_Detail VD
LEFT OUTER JOIN t_ServiceType ST
ON VD.ServiceTypeID = ST.TypeID
LEFT OUTER JOIN t_Service S
ON VD.ServiceID = S.ID
LEFT OUTER JOIN t_Brand B
ON VD.BrandID = B.ID
LEFT OUTER JOIN t_Color CL
ON VD.ColorID = CL.ID
WHERE
V.VoucherID= VD.VoucherID
AND V.StatusFlag = 0 ' IF @VoucherID<>''
SET @strSQL = @strSQL +' AND V.VoucherID = ''' + @VoucherID +''''
IF @ClientTypeID <> ''
SET @strSQL = @strSQL +' AND V.ClientTypeID = ''' + @ClientTypeID +''''
IF @ClientID <> ''
SET @strSQL = @strSQL +' AND V.ClientID = ''' + @ClientID +''''
IF @BeginDate <> ''
SET @strSQL = @strSQL +' AND CONVERT(varchar(10),V.ReceiveDate,111) >= ''' + @BeginDate +''''
IF @EndDate <> ''
SET @strSQL = @strSQL +' AND CONVERT(varchar(10),V.ReceiveDate,111) <= ''' + @EndDate +''''
IF @ServiceTypeID <> ''
SET @strSQL = @strSQL +' AND VD.ServiceTypeID = ''' + @ServiceTypeID +''''
IF @ServiceID <> ''
SET @strSQL = @strSQL +' AND VD.ServiceID = ''' + @ServiceID +''''
IF @SettleFlag <> ''
SET @strSQL = @strSQL +' AND V.SettleFlag = ' + @SettleFlag
IF @TakeFlag <> ''
SET @strSQL = @strSQL +' AND VD.StatusFlag = ' + @TakeFlag
SET @strSQL = @strSQL + ' ORDER BY V.VoucherID, V.ReceiveDate'
EXEC(@strSQL)
GO
/**************************************/
/* 学无止境 */
/**************************************/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货