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?

data jockey · Accepted Answer · 2009-10-15 00:34:37Z

4

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

answered Oct 15, 2009 at 0:34

data jockey

1412 bronze badges

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

2 Comments

Dustin Hodges Over a year ago

Thanks this worked. I tried this before without the alias and it got a syntax error. Do you know why the a is required at the end?

data jockey Over a year ago

Glad I could help. I can't say exactly why there is a need for an alias, except to say that all fields must be able to be fully-qualified (most of the time, the engine handles this for you), and without a name for the derived table/resultset, you have no name for the source of any referenced fields. Just guessing, though.

Erich · Accepted Answer · 2009-10-14 21:30:46Z

4

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

answered Oct 14, 2009 at 21:30

Erich

3,9721 gold badge25 silver badges21 bronze badges

2 Comments

Dustin Hodges Over a year ago

Unfortunately that will just give me the count of how many different tests a particular row failed; e.g. if UniqueIdentifier=1 and fkJobID=1 and TableName='table1' and that row failed fkValidationFieldFailureType 1, 6, and 7 Count(*) will return 3. Basically doing that returns a count for each row that failed a test

Cade Roux Over a year ago

You need to wrap it so that the COUNT(*) is the count of the groups, not the count within each group.

Jon Harbour · Accepted Answer · 2013-12-12 18:38:00Z

1

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 ...

answered Dec 12, 2013 at 18:38

Jon Harbour

4024 silver badges5 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related