CODEWITHSUNDEEP
sqldatabasevisual-studio
Andrew McMenemy
Andrew McMenemy

Reputation: 39

SQL Stored Procedure not working as intended.

I've been using Visual Studio 2013 to make a simple Customer database, and things have been going well. I had a search query that worked, but I am now trying to use it as a stored procedure. This is because there's a little more to it, but I just want to solve this basic version of the problem first. The Stored Procedure only returns when the variable @Name is exactly equal to a name in the column "Name".

e.g searching for "And" should return the rows of customers whose names begin with And, such as Andrew, but it does not. It does succeed when searching "Andrew" in it's entirety.

ALTER PROCEDURE [dbo].[SP_CustomerQuery] (
     @Name NCHAR(25) = NULL
     )
AS
SELECT Name
       ,Surname
       ,Email
       ,MobileNo
       ,CustomerID
       ,PhoneNo
       ,AreaCode
       ,Address
       ,CompanyName

FROM CUSTOMERS
WHERE (@Name IS NULL OR Name LIKE @Name + '%')

I am executing the query from within Visual Studio to debug so it's not my C# Code causing any mischief. Would love some help, as I was sure this was the correct query. (I have also tried 

WHERE (@Name IS NULL OR Name LIKE '%' + @Name + '%')

Upvotes: 0

Views: 229

Answers (1)

Steve
Steve

Reputation: 216273

You are passing a parameter of type NCHAR.
This type has always the length specified of 25 chars

So when you pass "And" in reality the SP sees "And " and of course the search fails, while for the same reason "Andrew" works.

You should change your SP to receive a NVARCHAR instead

However, having a column of type NCHAR to store a Name data seems just a problem to be avoided from the start. Unless you have a very compelling reason to have all names of the same length and filled with spaces if they are shorter then I suggest you to change this field to NVARCHAR.

You can read more about NCHAR vs NVARCHAR searching problems in this question SQL Server char and nchar columns search differently

Upvotes: 1

Related Questions