0

I am trying to run the following SQL script using Java and am getting issues with no resultset from JDBCTemplate. I thought about reducing it using functions/stored procedures and would like some help with it:

SQL - first part:

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');

Second part: 

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

I was thinking first part can be done in a stored procedure. I did read here (stored procedure with variable number of parameters) about making a table from a list of variables but am not sure how to do set those variables in a loop like i am doing from above (1,Variable1 etc.).

I think the second part can be by itself?

So my updated query might be:

  1. Call stored procedure (variable1, ..., variablex);
  2. SQL part 2?

If anyone can help that would be great!

Improve this question
0

1 Answer 1

Reset to default
1

It's possible to do this in two seperate batches, but only if you can ensure that the first batch runs in session scope, and not in a nested batch ( eg via sp_executesql ). Temp tables created in nested batches, like stored procedures or prepared statements are automatically destroyed at the end of the nested batch. So it depends on how you call it. My guess is that a PreparedStatement won't work.

The right way to do this is probably to use a stored procedure with a table-valued parameter, or a JSON (for SQL 2016+), or XML parameter and parses it in the stored procedure body. See https://learn.microsoft.com/en-us/sql/connect/jdbc/using-table-valued-parameters?view=sql-server-2017

You can also use a TSQL batch instead of a stored procedure and bind a Table-Valued Parameter, or a NVarchar(max) parameter containing JSON.

With a TVP you could simply use a batch like:

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
    )

That would be the value of the string variable sql, and then call it something like:

SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement) connection.prepareStatement(sql); 
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject); 
ResultSet rs = stmt.executeQuery();
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I am using SQL server 2012. I actually tried to run this In jdbc but kept getting errors. So i am a bit stuck. I am not sure how i can guarantee the temp table not being destroyed though? Is it possible to use a real table and update it then delete all records? Not optimal but I am not sure how to go about it.
And how do I do this with the variable parameters ? Is the example I mentioned before valid? Thanks.
Table-Valued Parameters and XML are both supported by SQL Server 2012.
How would I call it afterwards if I want to guarantee the datat is there? Should I use real table?
The point of using a TVP is that you can use a single statement to both send the multiple rows of data and use it in a query.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.