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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply