With a T-SQL function, how can I return an integer from a count(*) function?

Question

I am simply trying to return the count(*) integer value that is produced from the select. This is my first attempt at functions.

I have tried my inner statements out of the function and all works as I expect. This returns the table, but what I really want is just to return an integer of what the count(*) returns. How can I do this?

CREATE FUNCTION dbo.LogicMatchCount
    (@logic varchar(1000),
      @tags varchar(1000) )
 RETURNS TABLE
 AS 
    RETURN  
        WITH  LogicTagTable AS 
        (
             SELECT TRIM(VALUE) AS logic_tag
             FROM string_split(@logic, '&')
         ),
         DonorTagTable AS 
        (
           SELECT TRIM(VALUE) donor_tag
           FROM string_split(@tags, ',')
         )              
        SELECT COUNT(*) AS TagMatchCount
         FROM LogicTagTable ltt
         INNER JOIN DonorTagTable dtt ON dtt.donor_tag = ltt.logic_tag;

Thanks!

Why are you returning a table - if you really only want to return a single atomic value ?? — marc_s
– marc_s, Commented Sep 27, 2020 at 19:52
I don't know how to do it. I tried RETURNS INT but then it gives an error when returning -- something to do with the WITH statement??? — Sm00thSailn
– Sm00thSailn, Commented Sep 27, 2020 at 19:53
Ha, it was because the statement preceding the common table expression wasn't terminated with a semicolon. If you don't want declare a variable, per marc_s's answer below, just add set nocount on; as the first line of the scalar function — SteveC
– SteveC, Commented Sep 27, 2020 at 21:04

marc_s · Accepted Answer · 2020-09-27 19:54:50Z

1

Since you really only want to return a single atomic value - use a scalar-valued function instead:

CREATE FUNCTION dbo.LogicMatchCount
    (@logic varchar(1000),
     @tags varchar(1000))
RETURNS INT
AS 
BEGIN
    DECLARE @CountResult INT;
    
    WITH LogicTagTable AS 
    (
        SELECT TRIM(VALUE) AS logic_tag
        FROM string_split(@logic, '&')
    ),
    DonorTagTable AS 
    (
        SELECT TRIM(VALUE) donor_tag
        FROM string_split(@tags, ',')
    )
    SELECT @CountResult = COUNT(*) 
    FROM LogicTagTable ltt
    INNER JOIN DonorTagTable dtt ON dtt.donor_tag = ltt.logic_tag;
    
    RETURN @CountResult;
END;

and then call it like this:

SELECT dbo.LogicMatchCount('....', '....');

answered Sep 27, 2020 at 19:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

Attie Wagner Over a year ago

@Sm00thSailn, remember to mark Marc_S' answer as the correct answer...

John Cappelletti · Accepted Answer · 2020-09-27 19:57:20Z

1

Perhaps slightly different approach

CREATE FUNCTION dbo.svfLogicMatchCount
    (@logic varchar(1000),
     @tags varchar(1000)   )
Returns int as  
Begin 
   Return ( 
            SELECT COUNT(*) 
            FROM ( SELECT TRIM(VALUE) AS logic_tag
                     FROM string_split(@logic, '&')
                 ) ltt
            INNER JOIN (
                          SELECT TRIM(VALUE) donor_tag
                          FROM string_split(@tags, ',')
                       ) dtt ON dtt.donor_tag = ltt.logic_tag
          )
End

answered Sep 27, 2020 at 19:57

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

2 Comments

Sm00thSailn Over a year ago

Very concise. Thanks.

Raymond Holmboe Over a year ago

Strange that begin .. end is needed when returning a scalar value but not a table

Collectives™ on Stack Overflow

With a T-SQL function, how can I return an integer from a count(*) function?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related