Conversion failed when converting from a character string to uniqueidentifier from A store Procedure in SQL database

Question

Getting below error when I connect a stored procedure using JDBC template in my Spring boot application. Error getting only for the accIds parameter

Resolved [org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback;
uncategorized SQLException for SQL [{call prc_Test(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [S0002]; error code [8169]; Conversion failed when converting from a character string to uniqueidentifier.]

Code:

String aa= "aa95dc9c-0c75, b96bc70b-858e";
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("prc_Test");

Map<String, Object> inParamMap = new LinkedHashMap<String, Object>();
//inParamMap.put("accIds", lst.stream().collect(Collectors.joining(",")));  
inParamMap.put("accIds", aa);

This is the stored procedure in the Azure SQL database:

CREATE PROCEDURE [dbo].[prc_Test]
    @accIds varchar(max) = '',
    @paSize int = 10,
    @pagecount int =1,
    @isOpenCases bit =1 ,
    @comId VARCHAR(MAX)='#',
    @casestatus VARCHAR(MAX)='#',
AS
BEGIN
    -- .............................
    
    FROM c_test2
    WHERE accIds IN (SELECT CAST(value AS uniqueidentifier) 
                     FROM string_split (@accIds,',') )
      AND (@comId = '#' OR [test_comp] IN (SELECT VALUE 
                                           FROM string_split (@comId,',')))
    -- ....................................
    
    -- ....................................
    FROM c_test2
    WHERE accIds IN (SELECT CAST(value AS uniqueidentifier) 
                     FROM string_split (@accIds,',') )
      AND (@comId = '#' OR [test_comp] IN (SELECT VALUE 
                                           FROM string_split (@comId,',')))
    -- ....................................
    -- ....................................
    ORDER BY test_data DESC
        OFFSET (@pagecount-1) * @pagesize ROWS
        FETCH NEXT @pagesize ROWS ONLY

    RETURN 0
END

Any idea why the errors occur?

Answer 1

Conversion failed when converting from a character string to uniqueidentifier.

The provided string:

String aa = "aa95dc9c-0c75, b96bc70b-858e";

does not contain valid unique identifier numbers. This may be the reason for the error above. You can try the code below to call a stored procedure with valid unique identifiers:

EXEC [dbo].[prc_Test]
    @accIds = '3F2504E0-4F89-11D3-9A0C-0305E82C3301,3F2504E0-4F89-11D3-9A0C-0305E82C3302',
    @paSize = 2,
    @pagecount = 1,
    @isOpenCases = 1,
    @comId = 'CompanyA',
    @casestatus = '#';

This returns a valid row as shown below: