Функция TSQL для возврата количества строк в результирующем наборе из функции.

У меня есть задание, которое выполняет несколько проверок строк в нескольких таблицах базы данных. Если проверка не удалась, она регистрирует ошибку в таблице, которая. Регистрируемая информация включает в себя имя таблицы, значение уникального идентификатора строки, в которой произошел сбой, проверку ошибки и задание, которое выполнялось в данный момент. Вот упрощенное определение таблицы журнала

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)

Я хотел бы написать функцию, которая возвращает количество неудачных строк с учетом определенной таблицы и идентификатора задания (например, fnGetNumberOfFailedRows(@JobID, @TableName)). Я пробовал что-то похожее на следующее:

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

Но, конечно, вы не можете иметь этот оператор Select внутри функции. Есть ли способ сделать то, что я хочу, внутри функции или мне нужно использовать маршрут хранимой процедуры?


sql sql-server tsql user-defined-functions
person Dustin Hodges    schedule 14.10.2009    source источник

Ответы (3)


arrow_upward
4
arrow_downward

Это должно сделать это за вас:

    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
person data jockey    schedule 15.10.2009
comment
Спасибо, это сработало. Я пробовал это раньше без псевдонима и получил синтаксическую ошибку. Вы знаете, почему в конце требуется буква а? - person Dustin Hodges; 15.10.2009
comment
Рад, что смог помочь. Я не могу точно сказать, зачем нужен псевдоним, за исключением того, что все поля должны иметь возможность быть полностью определенными (в большинстве случаев движок обрабатывает это за вас), и без имени для производного table/resultset, у вас нет имени для источника каких-либо полей, на которые ссылаются. Впрочем, просто предположил. - person data jockey; 16.10.2009

arrow_upward
4
arrow_downward

Вы можете использовать оператор count(*) в своем выборе и назначить его правильно, например:

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
person Erich    schedule 14.10.2009
comment
К сожалению, это просто даст мне подсчет того, сколько разных тестов не удалось выполнить для конкретной строки; например если UniqueIdentifier=1 и fkJobID=1 и TableName='table1' и эта строка не прошла проверку fkValidationFieldFailureType 1, 6 и 7 Count(*) вернет 3. В основном это возвращает количество для каждой строки, которая не прошла тест - person Dustin Hodges; 15.10.2009
comment
Вам нужно обернуть его так, чтобы COUNT(*) был количеством групп, а не количеством внутри каждой группы. - person Cade Roux; 15.10.2009

arrow_upward
1
arrow_downward

В SQL Server 2008 и более поздних версиях добавьте COUNT(*) OVER() в качестве одного из имен столбцов в вашем запросе, и это будет заполнено общим количеством возвращенных строк. Он повторяется в каждой отдельной строке, но по крайней мере значение доступно. Причина, по которой многие другие решения не работают, заключается в том, что для очень больших наборов результатов вы не будете знать общее количество до тех пор, пока не выполните итерацию всех строк, что во многих случаях нецелесообразно (особенно решения для последовательной обработки). Этот метод дает вам общее количество, например, после вызова первого IDataReader.Read().

выберите COUNT(*) OVER () как Total_Rows,... из...

person Jon Harbour    schedule 12.12.2013