Problem with UDF - Doesn't return anything

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with UDF - Doesn't return anything

Author

Topic

Sschuster
Starting Member

26 Posts

Posted - 2005-12-21 : 16:02:06

When I test this function in query analyzer the only thing I get in the results grid is 'Command completed successfully' message. The function should return a REAL datatype. Am I missing something here?

Here is the UDF...
CREATE FUNCTION fn_GetClosePriceByCurrency
(
@CurrencyItem varchar(10),
@ToDate datetime
)
RETURNS REAL AS
BEGIN
DECLARE @CurrType nvarchar (1),
@Error INT,
@Close real,
@ClosePrice real

Select @CurrType = SettlementType
From PFX_Currency C
Where C.Item = @CurrencyItem

If @CurrType = 'N'
(Select @Close = ClosePrice
From PFX_CurrencyHistory H
Where H.CurrencyItem = @CurrencyItem
AND (Datediff( day, H.CloseDate, @ToDate ) = 0))
Else
(Select @Close = ClosePrice
From PFX_CurrencyHistory H
Where H.CurrencyPair = (Select SettlementPair
From PFX_Currency
Where Item = @CurrencyItem)
AND (Datediff( day, H.CloseDate, @ToDate ) = 0))

-- If we don't find a record in the CurrencyHistory table then use the Currency Table
If @Close Is Null
If @CurrType = 'N'
Begin
(Select @ClosePrice = ClosePrice
From PFX_Currency C
Where C.Item = @CurrencyItem)
End
Else
(Select @ClosePrice = ClosePrice
From PFX_Currency C
Where C.Pair = (Select SettlementPair
From PFX_Currency
Where Item = @CurrencyItem))
Else
Select @ClosePrice = @Close

RETURN @ClosePrice
END

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-21 : 16:08:33

Tell, how u run the Function
Also did u try by assigning a value (constant as 45.23) to @ClosePrice just before returning it & c what is going on ?

Also put Begin--End in all places as necessary!!

Sschuster
Starting Member

26 Posts

Posted - 2005-12-21 : 16:13:30

I'm just typing in the function name and supplying valid parameters into SQL Query Analyzer.

Sschuster
Starting Member

26 Posts

Posted - 2005-12-21 : 16:48:33

Even this simple function doesn't return anything (Just the 'Command completed successfully' statement to the results grid...

CREATE FUNCTION fn_GetClosePriceByCurrency
(
@CurrencyItem varchar(10),
@ToDate datetime
)
RETURNS NVARCHAR AS
BEGIN
DECLARE @CurrType nvarchar (1),
@Error INT,
@Close real,
@ClosePrice real

BEGIN
Select @CurrType = SettlementType
From PFX_Currency C
Where C.Item = @CurrencyItem
END

RETURN @CurrType

END

Sschuster
Starting Member

26 Posts

Posted - 2005-12-21 : 16:48:36

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-21 : 18:19:47

post the code you calling your function with. Are you sure it's not /*commented*/ or --commented?

Try this and see what you get:


use pubs
go
create function dbo.junk() returns int as
begin
	return cast(1 as int)
end
go
select dbo.junk() [testcall]
go
drop function junk

Be One with the Optimizer
TG

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-22 : 10:18:20

-- First, did u created the function , means can u c the function in the objects list
-- By running the function u mean running the above set of statements or run the function ?
1. check whether u get the return value by Assigning a constant to the return variable
2. Run the SQL with the hard coded values and check whether it returns any result. [not in a function but
directly
eg. Select SettlementType From PFX_Currency C Where C.Item = '--Known Value--'
3. If the above 2 gives a result, use that in the function as
Select @CurrType = SettlementType From PFX_Currency C Where C.Item = '--Earlier Used Value--'

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-22 : 11:19:56

Yes Sschuster,

I found the following:

alter FUNCTION CubicVolume( )
RETURNS decimal(12,3)
AS
BEGIN
declare @p decimal(12,3)
set @p = 12.44
RETURN @p
END

What I found about UDF

CubicVolume () -- Line 1: Incorrect syntax near ')'.
CubicVolume -- The command(s) completed successfully.
dbo.CubicVolume () -- Line 1: Incorrect syntax near ')'.
dbo.CubicVolume -- The command(s) completed successfully.
By using Exec in front of each of the above gives the same results as above

print CubicVolume () -- 'CubicVolume' is not a recognized function name.
Select CubicVolume () -- 'CubicVolume' is not a recognized function name.

Following Works:

-------------------
print dbo.CubicVolume ()
Select dbo.CubicVolume ()

May be some other member will help to provide with a link the rationale behind this.

Sschuster
Starting Member

26 Posts

Posted - 2005-12-22 : 11:25:57

Got it!!

Apparently when you try to "run" the function in Query Analyzer you need to use SELECT 'FunctionName' for it to work correctly.

I was just typing the FunctionName part and expecting it to work. It's the little things I guess.

Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources