sqlsql-serverstored-proceduressoundex

Why is my stored procedure query returning extra results?

I have the following query inside of a stored procedure:

CREATE PROCEDURE [s_Staff_ByLikeLastNmByLikeFirstNm]  
  
 @LastNm varchar(10),  
 @FirstNm varchar(10)
  
 /*WITH ENCRYPTION*/  
  
AS  
  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  

SELECT
    Staff.FirstNm,
    Staff.LastNm
FROM
    Staff
WHERE
    Staff.LastNm LIKE @LastNm + '%'
    AND Staff.FirstNm LIKE @FirstNm + '%'

If I input 'Christiansen' in my query for @LastNm, when I execute the stored procedure, it is returning me both 'Christiansen' and 'Christianson', and performing more of a SOUNDEX search.

How can I fix this?

If I run do a select outside of the stored procedure, I am getting the correct results.

Solution

  • 'Christiansen' has 12 characters in it.

    You have defined the parameters to the stored procedure to have a length of 10, so the value is truncated to 'Christians'.

    Fix the length parameter in the declaration of the stored procedure.