Stored Procedure not returning all values when using Like-CodePudding

I'm building a .NET Application using SQL Server.

Currently, I am trying to get a stored procedure to return all rows with Titles similar to the given string, ignoring accents and allowing users to pass incomplete versions of the title.

E.g.

Poke, Poké, Pokémon, Pokemon, and Pokemon X, should all ideally return the row "Pokémon X". However, currently, none of the given parameters are returning anything. (Swagger UI just shows '[]' in the response body).

When I pass Pokémon X, the return Title is correct, but none of the other data is accurate (Id is 0, all other columns are null).

[
  {
    "id": 0,
    "title": "Pokémon X",
    "platform": null
  }
]

Game Table

CREATE TABLE [dbo].[Game] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Title]    NVARCHAR (100) NOT NULL,
    [Platform] NVARCHAR (50)  NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Stored Procedure

CREATE PROCEDURE [dbo].[spGame_GetSimilarTitles]
    @LikeTitle VARCHAR(100)
AS
BEGIN
    SELECT *
    FROM [dbo].[Game]
    WHERE [dbo].[Game].[Title] LIKE '%'   @LikeTitle   '%' COLLATE Latin1_General_CI_AI;
END

Function to retrieve data

 public Task<IEnumerable<Game>> GetSimilarTitles(string likeTitle) =>
            _db.LoadData<Game, dynamic>("dbo.spGame_GetSimilarTitles", new { LikeTitle = likeTitle });

API (Note: this is a simplified version)

 public async Task<IEnumerable<Game>> Test(string title)
        {
            var similarGames = await _games.GetSimilarTitles(title);

            return similarGames;
        }

LoadData function

public async Task<IEnumerable<T>> LoadData<T, U>(
            string storedProcedure,
            U parameters,
            string connectionId = "Default")
        {
            using IDbConnection connection = new SqlConnection(_config.GetConnectionString(connectionId));

            return await connection.QueryAsync<T>(storedProcedure, parameters,
                commandType: CommandType.StoredProcedure);
        }

CodePudding user response：

You need to make sure you're not switching between data types and collations for BOTH sides of the predicate (the column and your variable).

Consider:

DECLARE @Game TABLE (Id INT IDENTITY (1, 1) NOT NULL, Title NVARCHAR (100) NOT NULL, Platform NVARCHAR (50)  NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC));
INSERT INTO @Game (Title, Platform) VALUES ('Pokémon X', ''),('Pokemon Ruby', '')

DECLARE @LikeTitle NVARCHAR(100)

SET @LikeTitle = 'Poke'
SELECT *, @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI AS SearchedString
  FROM @Game
 WHERE Title COLLATE SQL_Latin1_General_Cp850_CI_AI LIKE '%'   @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI   '%'
 
SET @LikeTitle = 'Poké'
SELECT *, @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI AS SearchedString
  FROM @Game
 WHERE Title COLLATE SQL_Latin1_General_Cp850_CI_AI LIKE '%'   @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI   '%'

SET @LikeTitle = 'Pokémon'
SELECT *, @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI AS SearchedString
  FROM @Game
 WHERE Title COLLATE SQL_Latin1_General_Cp850_CI_AI LIKE '%'   @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI   '%'
 
SET @LikeTitle = 'Pokemon'
SELECT *, @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI AS SearchedString
  FROM @Game
 WHERE Title COLLATE SQL_Latin1_General_Cp850_CI_AI LIKE '%'   @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI   '%'

SET @LikeTitle = 'Pokemon X'
SELECT *,  @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI AS SearchedString
  FROM @Game
 WHERE Title COLLATE SQL_Latin1_General_Cp850_CI_AI LIKE '%'   @LikeTitle COLLATE SQL_Latin1_General_Cp850_CI_AI   '%'

Id  Title       Platform    SearchedString
--------------------------------------
1   Pokémon X               Poke
2   Pokemon Ruby            Poke

1   Pokémon X               Poké
2   Pokemon Ruby            Poké

1   Pokémon X               Pokémon
2   Pokemon Ruby            Pokémon

1   Pokémon X               Pokemon
2   Pokemon Ruby            Pokemon

1   Pokémon X               Pokemon X