CODEWITHSUNDEEP
sql-serverfunctiont-sql
Sm00thSailn
Sm00thSailn

Reputation: 33

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

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!

Upvotes: 1

Views: 519

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 1

marc_s
marc_s

Reputation: 755187

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('....', '....');

Upvotes: 1

Related Questions