代码之家 › 专栏 › 技术社区 › Berin Loritsch

在T-SQL中重用查询部分

tsql sql-server sql

-1

Berin Loritsch · 技术社区 · 8 年前

我有一个非常复杂的存储过程,它使用基于传入的某些值的不同where子句重复一个非常复杂的查询。存储过程占用了500多行代码,查询的公共部分只占用了100多行。该公共部分重复3次。

我最初想使用CTE(公共表表达式),除了在T-SQL中不能定义公共部分外,请执行以下操作: IF 语句,然后应用 WHERE

作为一种变通方法,我为通用代码创建了一个视图,但它仅用于一个存储过程。

有没有办法在不创建完整视图或临时表的情况下实现这一点?

理想情况下,我想做这样的事情:

WITH SummaryCTE (col1, col2, col3...)
AS
(
    SELECT concat("Pending Attachments - ", ifnull(countCol1, 0)) col1
    -- all the rest of the stuff
    FROM x as y
    LEFT JOIN attachments z on z.attachmentId = x.attachmentId
    -- and more complicated stuff
)

IF (@originatorId = @userId)
BEGIN
    SELECT * FROM SummaryCTE
    WHERE
       -- handle this security case
END
ELSE IF (@anotherCondition = 1)
BEGIN
    SELECT * FROM SummaryCTE
    WHERE
       -- a different where clause
END
ELSE
BEGIN
    SELECT * FROM SummaryCTE
    WHERE
       -- the generic case
END

希望伪代码能让你知道我想要什么。现在我的解决方法是为我定义的内容创建一个视图 SummaryCTE 然后处理 如果 / ELSE IF / ELSE 条款执行此结构将首先抛出一个错误 如果 语句,因为下一个命令应该是 SELECT 相反至少在T-SQL中。

也许这是不存在的,但我想确定。

1 回复 | 直到 8 年前

Becuzz 8 年前

好的,除了您已经识别的临时表和视图之外,您还可以使用动态SQL来构建代码,然后执行它。这使您不必重复代码,但只需处理就有点困难。这样地:

declare @sql varchar(max) = 'with myCTE (col1, col2) as ( select * from myTable) select * from myCTE'

if (@myVar = 1)
begin
    @sql = @sql + ' where col1 = 2'
end
else if (@myVar = 2)
begin
    @sql = @sql + ' where col2 = 4'
end
-- ...

exec @sql

另一种选择是将不同的where子句合并到原始查询中。

WITH SummaryCTE (col1, col2, col3...)
AS
(
    SELECT concat("Pending Attachments - ", ifnull(countCol1, 0)) col1
    -- all the rest of the stuff
    FROM x as y
    LEFT JOIN attachments z on z.attachmentId = x.attachmentId
    -- and more complicated stuff
)
select *
from SummaryCTE 
where 
(
    -- this was your first if
    @originatorId = @userId
    and ( whatever you do for your security case )
)
or
(
    -- this was your second branch
    @anotherCondition = 1
    and ( handle another case here )
)
or
-- etc. etc.

这消除了if/else链,但使查询更加复杂。由于参数嗅探,它还可能导致一些糟糕的缓存查询计划,但这可能与您的数据无关。在做决定之前测试一下。(您还可以添加优化器提示以不缓存查询计划。您不会得到一个坏的提示,但每次执行时也会受到影响以再次创建查询计划。测试以找出答案,不要猜测。此外,具有视图和if/else链的解决方案也会遇到相同的参数嗅探/缓存查询计划问题。)