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

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, 
           TableUniqueI开发者_如何转开发dentifier, 
           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?

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

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

In SQL Server 2008 and later, add COUNT(*) OVER () as one of the column names in your query and that will be populated with the total rows returned. It is repeated in every single row but at least the value is available. The reason why many other solutions do not work is that, for very large result sets, you will not know the total until after iterating all rows which is not practical in many cases (especially sequential processing solutions). This technique gives you the total count after calling the first IDataReader.Read(), for instance.

select COUNT(*) OVER () as Total_Rows, ... from ...