Getting different results from LIKE query and stored procedure for (starts and ends with) search

I am trying to implement a stored procedure that gets the two parameters @startsWith and @endsWith and constructs this query string:

@startswith + '%' + @endsWith

To search for entries of a single column (Name) that start end end with the parameters. Here is the stored procedure:

CREATE PROCEDURE termNameStartsEndsWith(
    @startsWith AS nvarchar, 
    @endsWith   AS nvarchar
)
AS
BEGIN
    SELECT * FROM Term WHERE
        Name LIKE (@startsWith + '%' + @endsWith) 
END;

However, I get unexpected results when one of the two query parameters is empty (''). Here is an example where I would expect only results where the Term column entry starts with ‘water’, but i get a bunch of additional rows:

I dont get these results when executing as a query:

So I expect that the problem is coming from the empty string concatenation being handled differently in a stored procedure? If so, how can I adapt the procedure accordingly?

Thanks for the help in advance.

Answer

As noted by Larnu in the comments, the issue isn’t the query, it’s your parameter declarations.

You have two NVARCHAR(n) parameters declared, but there is no length declared for either of them. From the documentation (emphasis added):

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

So both parameters are exactly one character long. Conveniently, SQL Server will let you assign a longer value to that parameter, and then just take the first character and silently truncate the rest.

Modify your parameters to have length definitions, and you should be in business.