sp_executesql Not Working with a Parameters search nvarchar

Question

I try to make my problem to sample. Something like this:

CREATE TABLE test
(
    [No] [bigint] IDENTITY(1, 1) PRIMARY key,
    [Title] [nvarchar](100) NOT NULL
) 
GO

INSERT INTO test(Title)
    SELECT '개인경비 청구서 작성 및 교육'
    UNION ALL
    SELECT 'a'
    
CREATE PROCEDURE [dbo].[Notice_Dels]            
    @SerchText  NVARCHAR(200)
AS 
BEGIN
    DECLARE @Query NVARCHAR(MAX);

    SET @Query  =  N'SELECT N.No, N.Title
                     FROM test N 
                     WHERE N.Title LIKE N''%@SerchText%''   '

    PRINT @Query

    EXEC SP_EXECUTESQL @Query, N' @SerchText   NVARCHAR(200)', @SerchText   
END

EXEC [Notice_Dels] N'개인경비';

It returns no row. How can I fix it?

Answer 1

You are correctly attempting to debug your dynamic SQL by using the PRINT statement, but you don't appear to have checked that the SQL it produces is correct. Because this:

SELECT
    N.No, 
    N.Title
FROM test N 
WHERE N.Title LIKE N'%@SerchText%'   

Is searching for text containing the string '@SerchText' not the contents of the variable @SerchText. You need to change the LIKE line as follows - see how we are now concatenating the contents of @SerchText:

LIKE N''%'' + @SerchText + ''%''   '

This now produces the following SQL which I believe is what you require:

SELECT
    N.No, 
    N.Title
FROM test N 
WHERE N.Title LIKE N'%' + @SerchText + '%'  

Note your example data also has an issue, but I assume your actual data does not. You are inserting non-Unicode data in your example (which also doesn't work) e.g.

SELECT '개인경비 청구서 작성 및 교육'

whereas you should be inserting Unicode data e.g.

SELECT N'개인경비 청구서 작성 및 교육'

DBFiddle