September 23, 2013 at 4:01 pm
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 🙂
September 23, 2013 at 4:28 pm
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/
September 23, 2013 at 4:35 pm
There an issue around the If. I get the error, expecting ID?
September 23, 2013 at 4:40 pm
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/
September 23, 2013 at 4:56 pm
You should take a look at this article.
How to Make Scalar UDFs Run Faster[/url]
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
September 23, 2013 at 8:29 pm
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?
September 24, 2013 at 8:26 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply