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?
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'개인경비 청구서 작성 및 교육'