Search function stored procedure with multiple fields not returning expected results

I have a simple table with 4 rows as follows:

| person_ID | FirstName | LastName | Phone      | Email                 |
+-----------+-----------+----------+------------+-----------------------+
| 1         | Ernaldo   | Chin     | 0000000000 | echin@blah.blah       |
| 2         | Esmerelda | Chin     | 1111111111 | echin@idk.org         |
| 1002      | Terry     | Bogard   | 2222222222 | tbogard@fatalfury.com |
| 1003      | Captain   | Falcon   | 3333333333 | cfalcon@fzero.com     |

When I run the following query the results are exactly as expected: Query

DECLARE @firstname varchar(50) = NULL,
        @LastName varchar(50) = 'Chin',
        @person_ID int = null,
        @phone varchar(50) = null,
        @email varchar(50) = null

SELECT 
    [person_ID], [FirstName], [LastName], [Phone], [Email] 
FROM 
    [dbo].[Persons]
WHERE
    (@person_ID IS NULL OR person_ID = @person_ID)
    AND (@firstname IS NULL OR FirstName LIKE '%' + @firstname + '%')
    AND (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')

Results

| person_ID | FirstName | LastName | Phone      | Email                 |
+-----------+-----------+----------+------------+-----------------------+
| 1         | Ernaldo   | Chin     | 0000000000 | echin@blah.blah       |
| 2         | Esmerelda | Chin     | 1111111111 | echin@idk.org         |

BUT I created a stored procedure as follows:

CREATE PROCEDURE [dbo].[Persons_Get]
    @person_ID int = NULL,
    @FirstName varchar = NULL,
    @LastName varchar = NULL,
    @Phone varchar = NULL,
    @Email varchar = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        [person_ID], [FirstName], [LastName], [Phone], [Email] 
    FROM 
        [dbo].[Persons]
    WHERE
        (@person_ID IS NULL OR person_ID = @person_ID)
        AND (@Firstname IS NULL OR FirstName LIKE '%' + @Firstname + '%')
        AND (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')

and execute it like this

EXEC Persons_Get @LastName = 'Chin'

I get these results

| person_ID | FirstName | LastName | Phone      | Email                 |
+-----------+-----------+----------+------------+-----------------------+
| 1         | Ernaldo   | Chin     | 0000000000 | echin@blah.blah       |
| 2         | Esmerelda | Chin     | 1111111111 | echin@idk.org         |
| 1003      | Captain   | Falcon   | 3333333333 | cfalcon@fzero.com     |

Am I going crazy here? I’ve already spent way too much time on this. Is there something I’m doing wrong? I might end up using dynamic SQL instead but I need to know what I’m doing wrong here.

Answer

You MUST at all times specify an explicit length for any CHAR, NCHAR, VARCHAR, NVARCHAR parameters! Otherwise they default to 1 character length!

Use this:

CREATE PROCEDURE [dbo].[Persons_Get]
    @person_ID int = NULL,
    @FirstName varchar(50) = NULL,
    @LastName varchar(50) = NULL,
    @Phone varchar(50) = NULL,
    @Email varchar(255) = NULL
AS

and your stored procedure will work just fine

Advertisement

Answer