Solved

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

Posted on 2004-04-07
8
1,357 Views
Last Modified: 2007-12-19
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
Comment
Question by:YLI
  • 3
  • 3
  • 2
8 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
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

by:YLI
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.

Appreciated your further help...


0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10778568
please post your amendments.......

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 26

Expert Comment

by:Alan Warren
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

by:YLI
ID: 10778957
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(@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

by:Alan Warren
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

by:YLI
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

by:
Alan Warren earned 250 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Creating Records Where There Are None2 - The Sequel 6 27
Webservices in T-SQL 3 31
create an aggregate function 9 34
Find results from sql within a time span 11 30
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question