CREATE FUNCTION [dbo].[fnA]
(
@p_ProviderId CHAR(10),
@p_ProviderName NVARCHAR(256),
@p_DeliveryId CHAR(10),
@p_DeliveryName NVARCHAR(75),
@p_FileId CHAR(10),
@p_RawFileName NVARCHAR(256),
@p_ProcessStatus TINYINT,
@p_StartDownloadDate SMALLDATETIME,
@p_EndDate SMALLDATETIME,
@p_ParseRateBegin DECIMAL(5, 2),
@p_ParseRateEnd DECIMAL(5, 2),
@p_UserId INT
)
RETURNS TABLE
AS
RETURN
SELECT a.FileId AS r_PK,
a.FileId AS r_FileId,
b.RawFileName AS r_RawFileName,
c.DeliveryName AS r_DeliveryName,
c.ProviderDeliveryId AS r_ProviderDeliveryId,
d.CompanyName AS r_CompanyName,
d.ProviderId AS r_ProviderId,
b.DownloadDate AS r_DownloadDate,
a.ParseDate AS r_ParseDate,
b.RawFileDate AS r_RawFileDate,
e.Email AS r_Email,
b.LastUpdate AS r_LastUpdate,
a.Status AS r_Status,
a.ParseRowsRate AS r_ParseRowsRate,
a.ParseDataDetail AS r_ParseDataDetail,
a.ImportDataDetail AS r_ImportDataDetail,
dbo.fn_UserIdMappingToString(d.ProviderId, 256) AS r_Analysts
FROM dbo.PerformanceFileStatus a
INNER JOIN DB1.dbo.ProviderRawFile b ON a.FileId = b.FileId
INNER JOIN DB1.dbo.ProviderDelivery c ON b.ProviderDeliveryId = c.ProviderDeliveryId
INNER JOIN DB1.dbo.ClientCompany d ON c.ProviderId = d.ProviderId
LEFT JOIN DB1.dbo.UserSearch e ON b.UserId = e.UserId
WHERE a.Status <> 9
AND ( a.FileId = @p_FileId OR @p_FileId IS NULL
AND b.DownloadDate BETWEEN ISNULL(@p_StartDownloadDate, CONVERT(CHAR(10), GETDATE(), 120)) AND ISNULL(@p_EndDate, CONVERT(CHAR(10), GETDATE(), 120) + ' 23:59:00')
AND (@p_ProviderId IS NULL OR d.ProviderId = @p_ProviderId)
AND (@p_ProviderName IS NULL OR CONTAINS(d.CompanyName,@p_ProviderName))
AND (@p_DeliveryId IS NULL OR c.ProviderDeliveryId = @p_DeliveryId)
AND (@p_DeliveryName IS NULL OR CONTAINS(c.DeliveryName,@p_DeliveryName))
AND (@p_RawFileName IS NULL OR CONTAINS(b.RawFileName,@p_RawFileName))
AND (@p_ProcessStatus IS NULL OR (@p_ProcessStatus = 0 AND a.Status = 0)
OR (@p_ProcessStatus = 1 AND a.Status IN (1, 2, 12))
OR (@p_ProcessStatus = 2 AND a.Status IN (4, 11))
OR (@p_ProcessStatus = 3 AND a.Status IN (3, 13)))
AND ( @p_ParseRateBegin IS NULL OR a.ParseRowsRate >= @p_ParseRateBegin )
AND ( @p_ParseRateEnd IS NULL OR a.ParseRowsRate <= @p_ParseRateEnd )
AND ( @p_UserId IS NULL
OR EXISTS ( SELECT 1
FROM DB1.dbo.UserIdMapping f
WHERE ( d.ProviderId = f.Id AND f.IdType = 256 AND f.UserId = @p_UserId ))))
使用LIKE改为CONTAINS全文检索。使用之前必须创建全文检索。具体查看帮助。
