IF EXISTS
(SELECT * FROM sysobjects
WHERE type = 'P '
AND name = 'fn_getStoreStatus ')
DROP PROCEDURE fn_getStoreStatus
go
CREATE FUNCTION "DBA ". "fn_getStoreStatus "(IN @StoreID varchar(72) )
RETURNS varchar(40)
NOT DETERMINISTIC <================+++++++++++++
/*
FUNCTION: fn_getStoreStatus
AUTHOR: @@@@
DATE: 05/19/2006
DESCRIPTION: Get the store status
INPUTS: StoreId
OUTPUTS: Status
MODIFIED: n/a
*/
BEGIN
DECLARE @StoreStatus varchar(40);
declare @StoreAK varchar(64);
declare @ApprovalStatusCd varchar(40);
select @StoreAK = StoreAK from tblStore where StoreID = @StoreID;
select @ApprovalStatusCd = ApprovalStatusCd from tblStoreApproval where StoreID = @StoreID and activeflg = '1 ';
if (@ApprovalStatusCd = fn_getInternalCode( 'StoreApprovalStatusCd ', 'Rejected ')) then
set @StoreStatus= 'rejected ';
else
if(@ApprovalStatusCd = fn_getInternalCode( 'StoreApprovalStatusCd ', 'NewStore ')) or (@ApprovalStatusCd = fn_getInternalCode( 'StoreApprovalStatusCd ', 'Submitted ') and @StoreAK is null) then
/*new store*/
set @StoreStatus= 'new ';
else
if(@ApprovalStatusCd = fn_getInternalCode( 'StoreApprovalStatusCd ', 'Submitted ') or @ApprovalStatusCd = fn_getInternalCode( 'StoreApprovalStatusCd ', 'Pending ')) and @StoreAK is not null then
/*modify store*/
set @StoreStatus= 'modified ';
else
set @StoreStatus= ' ';
end if;
end if;
end if;
RETURN @StoreStatus
END;
上面的函数用到了NOT DETERMINISTIC，不过我个人认为不妥，认为没有必要使用。我参考了SABase 9的帮助文件
===========================================================
NOT DETERMINISTIC 子句 指定为 NOT DETERMINISTIC 的函数每次在查询中调用时都将重新求值。不是以这种方式指定的函数的结果可以高速缓存起来以便提高性能，并且每次在查询求值过程中使用相同的参数调用函数时，都会重用高速缓存的结果。