Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Access Function in SQL Function, Help Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 21, Visits: 100
hello

I'm trying to convert an Access function into a SQL function and having a litlte problem with the syntax. Hopefully someone can point me in the right direction.

Below is the VB function from Access.

If [vType] = "618" Or [vType] = "617" Then 

Select Case vGrade
Case "DS*"
[Points] = 180
Case "DS"
[Points] = 150
Case "ME"
[Points] = 105
Case "PA"
[Points] = 45
Case "FL"
[Points] = 0
Case Else
[Points] = Null
End Select
End If

If [vType] = "622" Or [vType] = "623" Then

Select Case vGrade
Case "D*D*"
[Points] = 360
Case "D*D"
[Points] = 330
Case "DD"
[Points] = 300
Case "DM"
[Points] = 240
Case "MM"
[Points] = 180
Case "MP"
[Points] = 120
Case "PP"
[Points] = 60
Case "FL"
[Points] = 0
Case Else
[Points] = Null
End Select
End If

If [vType] = "262" Then

Select Case vGrade
Case "DS*"
[Points] = 180
Case "DS"
[Points] = 150
Case "ME"
[Points] = 105
Case "PA"
[Points] = 45
Case "FL"
[Points] = 0
Case Else
[Points] = Null
End Select
End If

I have started creating the function but running into the syntax problems.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.tf_Points(@Grade AS VARCHAR(10), @Type AS INT)

IF @Type >0 BEGIN

SELECT
CASE
WHEN @Type = '618' Or @Type = '617' AND @Grade = 'DS*' THEN 180
WHEN @Type = '618' Or @Type = '617' AND @Grade = 'DS' THEN 150
WHEN @Type = '618' Or @Type = '617' AND @Grade = 'ME' THEN 105
WHEN @Type = '618' Or @Type = '617' AND @Grade = 'PA' THEN 45
WHEN @Type = '618' Or @Type = '617' AND @Grade = 'FL' THEN 0
ELSE NULL END
RETURN
END
ELSE

Or if there is a more efficient way of doing this, I'd welcome any ideas :)
Post #1497578
Posted Monday, September 23, 2013 4:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 344, Visits: 1,322
If @type is declared as an INT, I don't believe you need any kind of quotes around the actual numbers you are comparing it to.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.tf_Points(@Grade AS VARCHAR(10), @Type AS INT)

IF @Type = 618 or @Type = 617 BEGIN

SELECT
CASE
WHEN @Grade = 'DS*' THEN 180
WHEN @Grade = 'DS' THEN 150
WHEN @Grade = 'ME' THEN 105
WHEN @Grade = 'PA' THEN 45
WHEN @Grade = 'FL' THEN 0
ELSE NULL END
RETURN
END
ELSE



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1497587
Posted Monday, September 23, 2013 4:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 21, Visits: 100
There an issue around the If. I get the error, expecting ID?
Post #1497588
Posted Monday, September 23, 2013 4:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 344, Visits: 1,322
Knives85 (9/23/2013)
There an issue around the If. I get the error, expecting ID?


Your ELSE is dangling.


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1497589
Posted Monday, September 23, 2013 4:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 3,374, Visits: 7,297
You should take a look at this article.
How to Make Scalar UDFs Run Faster


Here's an example on what you could do.

CREATE FUNCTION dbo.tf_Points(
@Grade AS VARCHAR(10),
@Type AS INT
)
RETURNS TABLE
AS RETURN
SELECT CASE WHEN @Type IN(618, 617, 262)
THEN CASE WHEN @Grade = 'DS*' THEN 180
WHEN @Grade = 'DS' THEN 150
WHEN @Grade = 'ME' THEN 105
WHEN @Grade = 'PA' THEN 45
WHEN @Grade = 'FL' THEN 0 END
WHEN @Type IN(622, 623)
THEN CASE WHEN @Grade = 'D*D*' THEN 360
WHEN @Grade = 'D*D' THEN 330
WHEN @Grade = 'DD' THEN 300
WHEN @Grade = 'DM' THEN 240
WHEN @Grade = 'MM' THEN 180
WHEN @Grade = 'MP' THEN 120
WHEN @Grade = 'PP' THEN 60
WHEN @Grade = 'FL' THEN 0 END
END




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1497592
Posted Monday, September 23, 2013 8:29 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 710, Visits: 4,533
The line in there that would worry me is this one:

Scalar UDFs are generally accepted as one of the worst things you can do performance-wise in T-SQL. However, they may have earned a reputation that they simply don't deserve because a common method of measuring performance (SET STATISTICS TIME ON) injects a major performance problem of its own when used to measure the performance of Scalar UDFs.


Would it improve performance if this were converted to a view with some good indexing?
Post #1497623
Posted Tuesday, September 24, 2013 8:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 3,374, Visits: 7,297
I'm not sure on how would you manage to use a view if you want to use parameters. You could certainly use a lookup table, but the function I posted should perform fine. I believe that you can add the SCHEMABINDING option to make it deterministic.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1497902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse