Yongshu Li
asked on
Using OPTIONAL Parameter and CASE in a UDF on MS SQL Server 2000
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
Public Function fncGetReturnValue(Optional
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
ASKER
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.
Appreciated your further help...
"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.
Appreciated your further help...
please post your amendments.......
however sorry to actually get the default value used you need to call it as
dbo.fncGetReturnValue('def ault')
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..........
however sorry to actually get the default value used you need to call it as
dbo.fncGetReturnValue('def
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..........
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
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
ASKER
Alan - Thank you for making your suggested solution here.
Lowfatspread -
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(@in put,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(@i nput,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?
Lowfatspread -
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(@in
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(@i
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?
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
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
ASKER
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!!!
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!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(@in
End
Return (@fncReturnValue)
End