TSQL function to return the number of rows in a result set from a function

Question

I have a job that performs several validation checks on rows in several tables in a database. If the check fails it logs the failure in a table that. The information that is logged includes the table name, a uniqueidentifier value of the row that failed, which check it failed, and which job was being run at the time. Here's the simplified table definition of the log

CREATE TABLE [tblSY_ValidationFieldFailures](
    [pkValidationFieldFailure] [int] IDENTITY(1,1) NOT NULL,
    [fkJobID] [int] NOT NULL,
    [fkValidationFieldFailureType] [int] NOT NULL,
    [TableName] nvarchar(128),
    [TableUniqueIdentifier] [nvarchar](100) NULL)

I'd like to write a function that returns the number of rows failed given a certain table and jobID (i.e. fnGetNumberOfFailedRows(@JobID, @TableName)). I tried something similar to the following:

CREATE FUNCTION fnGetNumberOfRowsThatFailedValidationCheck 
(
    @pkJobID int,
    @TableName nvarchar(128)
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @NumFailedRows int

    Select fkJobID, 
           TableUniqueIdentifier, 
           TableName 
    From tblSY_ValidationFieldFailures
     Where fkJobID=@pkJobID And TableName=@TableName
                    Group By fkJobID, TableName, TableUniqueIdentifier

    SET @NumFailedRows = @@ROWCOUNT

    RETURN @NumFailedRows    
END

But of course you can't have that Select statement inside a function. Is there a way to do what I want inside a function or do I have to so the stored procedure route?

Answer 1

You could use a count(*) statement in your select, and assign that properly, such as:

CREATE FUNCTION fnGetNumberOfRowsThatFailedValidationCheck 
(
    @pkJobID int,
    @TableName nvarchar(128)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @NumFailedRows int

Select @NumFailedRows = count(*) 
From tblSY_ValidationFieldFailures
    Where fkJobID=@pkJobID And TableName=@TableName
                Group By fkJobID, TableName, TableUniqueIdentifier

--SET @NumFailedRows = @@ROWCOUNT

RETURN @NumFailedRows    
END

Answer 2

This should do it for you:

    CREATE FUNCTION fnGetNumberOfRowsThatFailedValidationCheck 
 (   
 @pkJobID int,    
 @TableName nvarchar(128)
 )
RETURNS int
AS

BEGIN

-- Declare the return variable here
DECLARE @NumFailedRows int

SELECT @NumFailedRows = count(*) 
FROM (
 Select 
  fkJobID,            
  TableUniqueIdentifier,            
  TableName 
 From tblSY_ValidationFieldFailures    
 Where fkJobID=@pkJobID
 And TableName=@TableName
 Group By fkJobID, TableName, TableUniqueIdentifier
 ) a

RETURN @NumFailedRows

END