x
Solved

Using OPTIONAL Parameter and CASE in a UDF on MS SQL Server 2000

Posted on 2004-04-07
Medium Priority
1,431 Views
How would you convert the following function in MS Access (error handling omitted) into a UDF?

Public Function fncGetReturnValue(Optional intA As Integer =1) As Currency

If IsMissing (intA) Then

fncGetReturnValue= 0

Else

Select Case intA

Case 1
fncGetReturnValue=x
Case 2
fncGetReturnValue = y
Case Else
fncGetReturnValue=z

End Select

End if

End Function
0
Question by:Yongshu Li
• 3
• 3
• 2

LVL 50

Expert Comment

ID: 10777659
create function fncGetReturnValue ( @Input Integer = 1)
returns money
as
Begin
Declare @fncReturnValue Money
Set @fncReturnValue = Case @input
When 1 Then x
when 2 then y
when 3 then z
Else convert(money,Coalesce(@input,0))
End
Return (@fncReturnValue)
End
0

Author Comment

ID: 10777895
Thank you for the help. It runs fine if I give a value to pass to @intA. Howver, I got the folloing errors if I don't provide any value to @intA when calling it on the SQL Analyzer (I replaced x,y,z with 1,2,3 respectively)

"An insufficient number of arguments were supplied for the procedure or function dbo.fncGetReturnValue" when I used the following calling syntax:

select dbo.fncGetReturnValue ()

Further, what changes in the UDF would you make if I cross out the following in the original Access function:

"Case 2
fncGetReturnValue = y"

Plus, let me change x,y,z to a value returned from a SELECT statement.

0

LVL 50

Expert Comment

ID: 10778568

however sorry to actually get the default value used you need to call it as

dbo.fncGetReturnValue('default')

e.g. either the word default or a specific value..

if you want to use a select statement within a function then the function must return a Table
and can't be a scalar function as we've currently defined..........
0

LVL 26

Expert Comment

ID: 10778775
Hi YLI

This might do for now, till you get something tighter.

CREATE Function [dbo].[YourFunction]
(
@intA int = 0
)

RETURNS varchar(1) AS
BEGIN
return
(
CASE
WHEN @intA = 0 THEN '0'
WHEN @intA =1 THEN 'x'
WHEN @intA =2 THEN 'y'
WHEN @intA =3 THEN 'z'
END

)
END

Alan
0

Author Comment

ID: 10778957
Alan - Thank you for making your suggested solution here.

The folloing is my amended udf based on your solution--

create function fncGetReturnValue ( @Input Integer = 1)
returns money
as

Begin

Declare @fncReturnValue Money

Set @fncReturnValue = Case @input
When 1 Then SELECT fld1 FROM tbl1 WHERE fld2 ='a'
when 2 then 2 SELECT fld1 FROM tbl1 WHERE fld2 ='b'
Else convert(money,Coalesce(@input,0))
End
Return (@fncReturnValue)
End

2 points:

1. I just learned that a UDF does'nt support OPTIONAL parameter. It seems either a variable value or the word DEFAULT has to be provided when calling it. To me, this is pain if I have to call this UDF by providing DEFAULT a million times.

2. Since I cannot use an OPTIONAL paramenter, I won't be able to have a complete "translation" from my original Access function, I'm afraid. So we might have to cross out the 'convert(money,Coalesce(@input,0))' part in the UDF. So the UDF might be as follows, with BUGS--

create function fncGetReturnValue ( @Input Integer = 1)
returns money
as

Begin

Declare @fncReturnValue Money

Set @fncReturnValue = Case @input
When 1 Then SELECT fld1 FROM tbl1 WHERE fld2 ='a'
ELSE SELECT fld1 FROM tbl1 WHERE fld2 ='b'
End
Return (@fncReturnValue)
End

Could anyone help with this amended UDF to work?
0

LVL 26

Expert Comment

ID: 10779444
Hi YLI

Select CusID, dbo.YourFunction(1) From tbl_Customers

returns:
4      x
7      x
9      x

Select CusID, dbo.YourFunction(null) from tbl_Customers
returns:

4      0
7      0
9      0

Alter Function [dbo].[YourFunction]
(
@intA int
)

RETURNS varchar(1) AS
BEGIN
return
(
CASE
WHEN (ISNULL(@intA, 0) = 0) THEN '0'
WHEN @intA =1 THEN 'x'
WHEN @intA =2 THEN 'y'
WHEN @intA =3 THEN 'z'
END

)
END

hth

Alan
0

Author Comment

ID: 10783010
Hi, Alan:

No error compiling the syntax, but will get the same error -- "An insufficient number of arguments were supplied for the procedure or function dbo.FunctionName" when calling it. Just have to supply a value or the word "DEFAULT" if setting up a default value.

BTW, the value to be returned in the SELECT part should be based on the value of @intA and will be a UNIQUE value or NULL...

Another Q --  Is there an equivalent function to NZ() on MS SQL Server 2000?

Any help from anybody would be appreciated!!!

0

LVL 26

Accepted Solution

Alan Warren earned 1000 total points
ID: 10788022

Use isnull just like NZ
WHEN (ISNULL(@intA, 0) = 0) THEN '0'

You might be better off using a sub-proc instead of a function for this YLI.

Like this:

-- Main Proc script ---
-- testing
-- exec spMainProc
Alter procedure spMainProc

as
declare
@taxRate Money,
@intA int

-- for testing
-- set @intA = 0
-- set @intA = 1
-- set @intA = 2
-- set @intA = 3
-- set @intA = 7

EXECUTE YourSubProc @intA,
@taxRate = @taxRate OUTPUT

Select @taxRate as 'rate'
Return
-- End Main proc ---

-- Sub Proc script ---
Alter Procedure [dbo].[YourSubProc]
(
@intA int = null,
@TaxRate money = null OUTPUT
)

As

BEGIN

( Select @TaxRate =
CASE
WHEN (ISNULL(@intA, 0) = 0) THEN 0
WHEN @intA =1 THEN .25
WHEN @intA =2 THEN .35
WHEN @intA =3 THEN .45
ELSE .66
END

)
END
-- testing
-- Select @TaxRate as 'Rate'

-- end sub proc script ---

Alan
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.