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: Tuesday, September 16, 2014 7:27 AM
Points: 21, Visits: 115
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 @ 6:12 PM
Points: 352, Visits: 1,446
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: Tuesday, September 16, 2014 7:27 AM
Points: 21, Visits: 115
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 @ 6:12 PM
Points: 352, Visits: 1,446
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 @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 773, Visits: 5,002
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 @ 2:04 PM
Points: 3,572, Visits: 8,008
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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