sql - 使用表值函数捕获所有查询
问题描述
我有一个相当长的 Sql 查询,根据用户的选择需要不同的 where 子句。
我做了一些搜索,这似乎是关于此事的最全面的文章。
但是,它没有提到使用表值函数来实现相同效果的选项,对我来说,这似乎是最优化的答案。
我错过了什么吗?
这是我用 TVF 编写的全部查询:
--These paramenters will always be passed
declare @tenantId int = 7;
declare @yesterday DateTime = dateadd(day,datediff(day,1,GETUTCDATE()),0);
declare @tomorrow DateTime = dateadd(day,datediff(day,-1,GETUTCDATE()),0);
--These are optional:
declare @parentId int = 230; --In this example, @parentId is populated.
declare @ownedById int = null; --In this example, @ownedBy is not populated.
--Simple if statement:
if @parentId is not null
BEGIN
if @ownedById is not null
BEGIN
Select * from GetItemInfos(@tenantId, @yesterday, @tomorrow)
where ParentId = @parentId and OwnedById = @ownedById;
END
ELSE
Select * from GetItemInfos(@tenantId, @yesterday, @tomorrow)
where ParentId = @parentId;
END
ELSE IF @ownedById is not null
BEGIN
Select * from GetItemInfos(@tenantId, @yesterday, @tomorrow)
where OwnedById = @ownedById;
END
ELSE
Select * from GetItemInfos(@tenantId, @yesterday, @tomorrow);
这是 TVF:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION GetItemInfos
(
@tenantId int,
@yesterday DateTime,
@tomorrow DateTime
)
RETURNS TABLE
AS
RETURN
(
Select i.Id,
i.AssignedToId,
i.ParentId,
i.OwnedById,
i.Budget,
i.CloseDate,
i.CloseStatusId,
AssignedToName = Concat(anu.FirstName, ' ', anu.LastName),
OwnedByName = Concat(anu2.FirstName, ' ', anu2.LastName),
CloseStatusName = cs.Name,
i.ItemTypeId,
ItemTypeIcon = it.Icon,
i.ImportanceTypeId,
ImportanceTypeDescription = ImportanceTypes.Description,
i.EntityStatusId,
i.DueDate,
IsOverdue =
case when i.CloseStatusId = 1 and i.DueDate <= @yesterday then Cast(1 as bit)
else Cast(0 as bit)
end,
IsDue =
case when i.CloseStatusId = 1 and i.DueDate >= @yesterday and i.DueDate < @tomorrow then cast(1 as bit)
else Cast(0 as bit)
end,
IsClosed = case when (i.CloseStatusId != 1) then cast(1 as bit) else cast(0 as bit) end,
i.Cost,
ItemTypeShowProperties = it.ShowProperties,
ItemTypeSortOrder = it.SortOrder,
PriorityTypeDescription = PriorityTypes.Description,
PriorityTypeSortOrder = PriorityTypes.SortOrder,
i.Name,
i.PriorityTypeId,
Progress = Convert(nvarchar, i.Progress) + '%',
i.SortOrder,
i.StartDate,
StatusTypeName = StatusTypes.Name,
i.TimeEstimate,
TimeScaleTypeName = TimeScaleTypes.Name
from Items i
left join AppUsers au
on i.AssignedToId = au.Id
left join AspNetUsers anu
on au.AspNetUserId = anu.id
left join AppUsers au2
on i.OwnedById = au2.Id
left join AspNetUsers anu2
on au2.AspNetUserId = anu2.id
left join CloseStatuses cs
on i.CloseStatusId = cs.id
left join ItemTypes it
on i.ItemTypeId = it.Id
left join ImportanceTypes
on i.ImportanceTypeId = ImportanceTypes.Id
left join PriorityTypes
on i.PriorityTypeId = PriorityTypes.Id
left join StatusTypes
on i.StatusTypeId = StatusTypes.Id
left join TimeScaleTypes
on i.TimeScaleTypeId = TimeScaleTypes.Id
where i.TenantId = @tenantid
)
GO
编辑 根据评论,这似乎是最好的写法:
Select * from GetItemInfos(@tenantId, @yesterday, @tomorrow)
where (@parentId is null or ParentId = @parentId) and (@ownedById is null or OwnedById = @ownedById) OPTION(RECOMPILE)
解决方案
推荐阅读
- php - 需要 Google App Engine 登录 Flex
- xml - jQueryMobile,在新页面 div 中附加 html
- go - ioutil.ReadAll 为 tar 阅读器提供 0 个字节
- opengl - OpenGL indicesBuffer 值得使用吗?
- linkedin - linkedin 底层连接已关闭
- python - Tkinter 在鼠标单击时更新多边形点
- javascript - 如何在每 3 位数字后添加逗号?
- flutter - bottomNavigationBar 只在热重载时做我想做的事
- sentry - 如何查看 Sentry 中的特定错误实例
- r - romove 列中具有特定值的行