存储过程:USE [SPMS]
GO/****** Object: StoredProcedure [dbo].[SP_GetCurrentRate] Script Date: 06/12/2010 15:14:46 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_GetCurrentRate]
-- Add the parameters for the stored procedure here
@RecievedDate datetime,
@Currency Varchar(20),
@Rate decimal(18,8) output
AS
--declare @Rate decimal(18,8)
declare @selMon varchar(6)
if @Currency<>'USD' and @Currency<>'GBP' and @Currency<>'JPY' and @Currency<>'EUR' and @Currency<>'CHF' and @Currency<>'CAD' and @Currency<>'AUD' and @Currency<>'SGD' and @Currency<>'HKD' and @Currency<>'CNY'
set @Rate=0.00000000
else
BEGIN
if @Currency='CNY'
set @Rate=1.00000000
else
set @Rate=(select RateValue from Sp_Rates where RateName=@Currency and RateMonth=dbo.f_getCalMonth(@RecievedDate))
END
--return @Rate
GO自定义函数:USE [SPMS]
GO/****** Object: UserDefinedFunction [dbo].[f_getCurrentRate] Script Date: 06/12/2010 15:15:50 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[f_getCurrentRate]
(
@RecievedDate datetime,
@Currency Varchar(20)
)
RETURNS decimal(18,8)
AS
BEGIN
declare @Rate decimal(18,8)
exec [dbo].[SP_GetCurrentRate] @RecievedDate,@Currency,@Rate output
return @Rate END
GO调用:use spmsselect dbo.f_getCurrentRate('2009-4-3','USD')调用函数错误:Only functions and some extended stored procedures can be executed from within a function.请问哪位高手帮忙解决一下!
GO/****** Object: StoredProcedure [dbo].[SP_GetCurrentRate] Script Date: 06/12/2010 15:14:46 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_GetCurrentRate]
-- Add the parameters for the stored procedure here
@RecievedDate datetime,
@Currency Varchar(20),
@Rate decimal(18,8) output
AS
--declare @Rate decimal(18,8)
declare @selMon varchar(6)
if @Currency<>'USD' and @Currency<>'GBP' and @Currency<>'JPY' and @Currency<>'EUR' and @Currency<>'CHF' and @Currency<>'CAD' and @Currency<>'AUD' and @Currency<>'SGD' and @Currency<>'HKD' and @Currency<>'CNY'
set @Rate=0.00000000
else
BEGIN
if @Currency='CNY'
set @Rate=1.00000000
else
set @Rate=(select RateValue from Sp_Rates where RateName=@Currency and RateMonth=dbo.f_getCalMonth(@RecievedDate))
END
--return @Rate
GO自定义函数:USE [SPMS]
GO/****** Object: UserDefinedFunction [dbo].[f_getCurrentRate] Script Date: 06/12/2010 15:15:50 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[f_getCurrentRate]
(
@RecievedDate datetime,
@Currency Varchar(20)
)
RETURNS decimal(18,8)
AS
BEGIN
declare @Rate decimal(18,8)
exec [dbo].[SP_GetCurrentRate] @RecievedDate,@Currency,@Rate output
return @Rate END
GO调用:use spmsselect dbo.f_getCurrentRate('2009-4-3','USD')调用函数错误:Only functions and some extended stored procedures can be executed from within a function.请问哪位高手帮忙解决一下!
USE [SPMS]
GO
/****** Object: UserDefinedFunction [dbo].[f_getCurrentRate] Script Date: 06/12/2010 15:32:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[f_getCurrentRate]
(
@RecievedDate datetime,
@Currency Varchar(20)
)
RETURNS decimal(18,8)
AS
BEGIN
declare @Rate decimal(18,8)
--declare @selMon varchar(6)
if @Currency<>'USD' and @Currency<>'GBP' and @Currency<>'JPY' and @Currency<>'EUR' and @Currency<>'CHF' and @Currency<>'CAD' and @Currency<>'AUD' and @Currency<>'SGD' and @Currency<>'HKD' and @Currency<>'CNY'
set @Rate=0.00000000
else
BEGIN
if @Currency='CNY'
set @Rate=1.00000000
else
set @Rate=(select RateValue from Sp_Rates where RateName=@Currency and RateMonth=dbo.f_getCalMonth(@RecievedDate))
if @Rate is null
set @Rate=0.00000000
END
return @Rate END
调用函数:select dbo.f_getCurrentRate('2009-4-3','USD')返回值:
8.63000000