title: TSQL function to return the number of rows in a result set from a function
body: <p>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</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>