用CTE和可变输入优化SQL查询

Question

我试图使用Java运行以下SQL脚本，并且正在从JDBCTEMPLATE中获得无结果的问题。我考虑过使用功能/存储过程减少它，并希望对其进行一些帮助：

sql-第一部分：

SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#tempSearch', 'U') IS NOT NULL
    DROP TABLE #tempSearch;
CREATE TABLE #tempSearch
(
    ID INT,
    Value VARCHAR(255)
)
INSERT INTO #tempSearch
VALUES (1, 'Variable1'), (2, 'Variabl2');  

第二部分：

WITH cte AS
(
    SELECT
        RoleID,
        ',' + REPLACE(REPLACE(GroupNames, ',', ',,'), ' ', '') + ',' GroupNames 
    FROM
        UserGroup_Role_Mapping
), cte2 AS
(
    SELECT
        cte.RoleID, 
        REPLACE(cte.GroupNames, ',' + Value + ',', '') AS GroupNames, 
        s.ID, s.Value 
    FROM
        cte
    JOIN
        #tempSearch s ON ID = 1
    UNION ALL
    SELECT
        cte2.RoleID, 
        REPLACE(cte2.GroupNames, ',' + s.Value + ',', '') AS l, 
        s.ID, s.Value
    FROM
        cte2
    JOIN
        #tempSearch s ON s.ID = cte2.ID + 1
)
SELECT 
    a.Role, a.Sort_Order, 
    a.Parent, a.Parent_ID, a.Parent_URL, 
    a.Child, a.Child_ID,a.Child_URL
FROM
    Config_View a
WHERE 
    a.Role IN (SELECT Name 
               FROM
                   (SELECT DISTINCT RoleID FROM cte2 WHERE LEN(GroupNames) = 0) tempRoles
               JOIN
                   User_Role ON tempRoles.RoleID = User_Role.ID
              ) 
DROP TABLE #tempSearch

我认为可以在存储过程中完成第一部分。我确实在这里阅读了有关从变量列表中制作表的表(存储的参数数量(，但不确定如何在循环中设置这些变量，就像我从上面做的那样(1，variable1等(。/p>

我认为第二部分本身可以？

所以我更新的查询可能是：

1. 呼叫存储过程(variable1，...，variablex(;
2. SQL第2部分？

如果有人可以提供帮助！

Answer 1

可以在两个分开的批处理中进行此操作，但是前提是您可以确保第一个批次在会话范围内运行，而不是在嵌套批处理中运行(例如，通过sp_executesql(。在嵌套的过程中创建的温度表，例如存储过程或准备好的语句在嵌套批处理结束时自动破坏。因此，这取决于您如何称呼它。我的猜测是准备序列无法正常工作。

正确的方法可能是使用带有表值参数的存储过程，或JSON(用于SQL 2016 (或XML参数，然后将其解析为存储过程主体。请参阅https://learn.microsoft.com/en-us/sql/connect/jdbc/ususe-table-valued-parameters?view = sql-server-2017

您也可以使用TSQL批处理而不是存储过程并绑定表值参数，或包含JSON的NVARCHAR(MAX(参数。

使用TVP，您可以简单地使用类似的批次：

with s as (
     select * from ? --bind a table-valued parameter here
     ), cte as (
    select RoleID,','+replace(replace(GroupNames,',',',,'),' ','')+',' GroupNames from UserGroup_Role_Mapping
    )
    ,cte2 as(
    select cte.RoleID, replace(cte.GroupNames,','+Value+',','') as GroupNames, s.ID, s.Value 
    from cte
    join s on ID=1
    union all
    select cte2.RoleID, replace(cte2.GroupNames,','+s.Value+',','') as l, s.ID ,s.Value
    from cte2
    join s on s.ID=cte2.ID+1
)
SELECT a.Role, a.Sort_Order, a.Parent, a.Parent_ID, a.Parent_URL, a.Child, a.Child_ID,a.Child_URL
FROM Config_View a
WHERE a.Role IN (
    Select Name from (
        Select distinct RoleID from cte2 where len(GroupNames)=0
    ) tempRoles
    join User_Role
    on tempRoles.RoleID = User_Role.ID
    ) 

那将是字符串变量sql的值，然后称其为：

SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement(sql); 
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject); 
ResultSet rs = stmt.executeQuery();