Count on SQL Function return value

Question

I am trying to count the number of records in my table that make a function I created return 1. Here is what I have:

CREATE FUNCTION isErrorMismatch
(@theType nvarchar(1), @theExCode nvarchar(2))
RETURNS bit
AS
BEGIN
    DECLARE @theTypeAsInt int
    SET @theTypeAsInt = CAST(@theExCode AS INT)

    DECLARE @returnValue bit
    SET @returnValue = 0

    IF @theType = 'A'
        IF @theTypeAsInt >= 10 AND @theTypeAsInt <= 17 
            SET @returnValue = 0
        ELSE
            SET @returnValue = 1
    ELSE IF @theType = 'B'
        IF @theTypeAsInt >= 18 AND @theTypeAsInt <= 26 
            SET @returnValue = 0
        ELSE
            SET @returnValue = 1
    ELSE IF @theType = 'C'
        IF @theTypeAsInt >= 30 AND @theTypeAsInt <= 38 
            SET @returnValue = 0
        ELSE
            SET @returnValue = 1
    ELSE
            SET @returnValue = 1

    RETURN @returnValue
END

GO

SELECT (SELECT COUNT(*) 
FROM isErrorMismatch(LEFT(Type, 1),LEFT([Exception Code/Category],2)) 
As MismatchCount
FROM dbo.[All Service Ticket Data 2012_final]

Every record that makes the function return 1, I want to count. I am getting syntax errors in my FROM when I call the function. Anyone have any ideas? Thank you!

***UPDATE:

In order to get the count that make the function return 1:

SELECT COUNT(dbo.isErrorMismatch(LEFT(Type, 1), LEFT([Exception Code/Category],2))) As MismatchCount
FROM dbo.[All Service Ticket Data 2012_final]
WHERE dbo.isErrorMismatch(LEFT(Type, 1), LEFT([Exception Code/Category],2)) = 1

In order to get all of the records that make the function return 1:

SELECT Type, [Exception Code/Category], 
dbo.isErrorMismatch(LEFT(Type, 1),LEFT([Exception Code/Category] ,2)) as Mismatch
FROM dbo.[All Service Ticket Data 2012_final]
WHERE dbo.isErrorMismatch(LEFT(Type, 1),LEFT([Exception Code/Category] ,2)) = 1

Is there also a way to display the records that make the function return 1? Rather than display just the counts? Thank you! — KateMak
– KateMak, Commented Mar 29, 2013 at 8:05

Oleksandr Fedorenko · Accepted Answer · 2013-03-29 08:52:55Z

2

Scalar UDF, which in this case accepted two parameters and returned a single value. Some of the areas where you can use a scalar UDF:

A column expression in a SELECT or GROUP BY
A search condition for a JOIN in a FROM clause
A search condition of a WHERE or HAVING clause

SELECT SUM(CAST(dbo.isErrorMismatch(LEFT(Type, 1), LEFT([Exception Code/Category],2)) AS int)) As MismatchCount
FROM dbo.[All Service Ticket Data 2012_final]

edited Mar 29, 2013 at 8:52

answered Mar 29, 2013 at 8:32

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

KateMak Over a year ago

Hello! thank you very much for posting! I am getting "The multi-part identifier "x.IsMatched" could not be bound." Can you explain what x is? Thank you!

Oleksandr Fedorenko Over a year ago

@user1461332 column "x.IsMatched" used in von v. answer, not my;)

KateMak Over a year ago

Oops! Sorry! In any case, this helped!

von v. Over a year ago

@user1461332 that is right SUM will not work because the returned type of your function is bit. This answer while I like it will not give you the records you need though. You still need to go with my answer to get the records that gives 1 using your function. But glad we were able to help in any way.

Oleksandr Fedorenko Over a year ago

@von v. In modified answer I convert bit into integer and then calculate sum. BTW +1 for good work!

von v. · Accepted Answer · 2013-03-29 08:17:49Z

2

You are having an error because your function is not a table type or does not return a table and so you can NOT select from it. But nevertheless you can achieve it by doing this:

SELECT  COUNT(*)            
FROM    dbo.[All Service Ticket Data 2012_final] a
        INNER JOIN
            (
            SELECT  isErrorMismatch(LEFT(Type, 1),LEFT([Exception Code/Category],2)) IsMatched, Your_PK_Column_or_Id
            FROM    dbo.[All Service Ticket Data 2012_final]
            ) x ON x.Your_PK_Column_or_Id = a.Your_PK_Column_or_Id
WHERE   x.IsMatched = 1

I just want to add that if the value you passed a value to @theExCode that cannot be cast to an INT then there will be an exception in your query.

edited Mar 29, 2013 at 8:17

answered Mar 29, 2013 at 8:10

von v.

17.1k5 gold badges62 silver badges89 bronze badges

2 Comments

KateMak Over a year ago

Hello! Thank you for posting! I am trying to understand this, and had a couple of questions. Do I have to replace the x with anything? Also, does the a represent each record from the table? I am not sure what the x and the a mean..Thank you for helping!!

von v. Over a year ago

x and a are just alias and I used it because you have a long table name. The query won't show each record because you said you needed the count. But if you want to get each record then just replace the COUNT(*) with *.

user20416 · Accepted Answer · 2013-03-29 08:14:52Z

1

You need to end the CREATE FUNCTION with GO. Also, your SELECT subquery needs a closing parenthesis at the end.

(Your question was about the syntax errors).

answered Mar 29, 2013 at 8:14

user20416

15.5k9 gold badges76 silver badges149 bronze badges

1 Comment

KateMak Over a year ago

Yes, with fixing those I was able to select the records that were making the function return 1! Thank you!!!

Collectives™ on Stack Overflow

Count on SQL Function return value

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related