Tovdb


Hi everyone,

I don't know where to ask this question, so I'm trying here.

I'm developing an application which interacts with a db through Stored Procedures on SQL Server 2005.

I've created a mass search functionality which goes through 2 tables with one string seeing if it can find something.

The stored procedure I use is the following:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[getPersonsByAll]

@Name nchar(50)

AS

BEGIN

SET NOCOUNT ON;

SELECT distinct tblPerson.ID, tblPerson.Name, tblPerson.Surname

FROM tblPerson INNER JOIN tblPersonMeta ON tblPerson.ID = tblPersonMeta.PersonID

WHERE

(

(

(tblPerson.Name like @Name) or

(tblPerson.Surname like @Name) or

(tblPerson.Email like @Name) or

(tblPerson.Website like @Name) or

(tblPerson.Address like @Name) or

(tblPerson.Country like @Name) or

(tblPerson.Comments like @Name) or

(tblPerson.DateTime like @Name) or

(tblPerson.Telephone like @Name) or

(tblPersonMeta.Value like @Name) or

(tblPersonMeta.Title like @Name) or

(tblPerson.Mobile like @Name)

) and Active = 0

)

END

The string that I use in this SP (@Name) is created in VS so that it contains a '%' sign at the front and the end.

Now this works perfectly except if I try to search for something in the tblPersonMeta.Title field and I REALLY don't get why. I've been starring at this for hours now.

If anyone has an idea how to solve this, anything at all.. , it would be very much appreciated.

If you need any more info to give advice (like table structures, c# code from VS, ..) just ask.

Thanks,

Tobias




Re: SP - Strange problem

hunchback


Tovdb,

How are we going to help if you did not post any info about the tables involved in this sp . The help should be in both ways.

AMB






Re: SP - Strange problem

Arnie Rowland

A couple of comments.

First, using leading wildcards in the search will negate the possibility of using any indexing on any of the columns. Expect this procedure to be a real performance 'dog'. Depending upon your server configuration and hardware and size of the tables involved, it could even bring the server 'to its knees' and cause significant disruption to other users.

Second, as 'hunchback' indicated, it is very difficult to diagnose something when we can't see all of the parts. For example, what is the datatype of personmeta.title

Third, I realize that you probably 'inherited' this database and don't have any responsibility for its condition. But when you can, please stop naming tables with 'tbl'. Gosh, just by looking at the code, we would know that the SELECT is working against a table object. (Besides, in the notion of efficiency, you will save three keystrokes every time you reference a table.







Re: SP - Strange problem

Tovdb

Sorry about that...

Here are the tables:

tblPerson

ID (int)

Name (nchar(50))

Surname (nchar(50))

Email (nchar(100))

Website (nchar(100))

Address (nchar(100))

Country (nchar(50))

Comments (nchar(200))

Photo (image)

DateTime (datetime)

Telephone (nchar(20))

Mobile (nchar(20))

Active (int)

tblPersonMeta

ID (int)

PersonID (int)

Title (nchar(30))

Value (nchar(200))

I actually made the database myself, so I have to apologise for the 'tbl' prefix ..Smile





Re: SP - Strange problem

Chris Howarth

You say that the proc 'works perfectly' when searching on all but the tblPersonMeta.Title column. What problems are you experiencing when you attempt to search on this column

Chris






Re: SP - Strange problem

Tovdb

The resulttable is just empty.. Tongue Tied



Re: SP - Strange problem

Manivannan.D.Sekaran

Change your parameter datatype from nchar to nvarchar

sample:

Code Snippet

Create Table #main (

[Id] int ,

[Name] nchar(50) ,

[DepId] int

);

Insert Into #main Values('1','Microsoft SQL Server 2000','1');

Insert Into #main Values('2','Microsoft Sql Server 2005','2');

Insert Into #main Values('3','ASP','1');

Insert Into #main Values('4','ASP.Net','2');

Create Table #dept (

[Id] int ,

[Title] nchar(30)

);

Insert Into #dept Values('1','Data base');

Insert Into #dept Values('2','Web App');

/*NO OUTPUT*/

Declare @Name nchar(50)

Set @Name = 'Data'

Set @Name = '%' + @Name + '%'

Select * from #main A Join #dept B ON A.DepId=B.id Where A.Name Like @Name Or B.Title LIke @Name

/*Some OUTPUT*/

Declare @Name2 nvarchar(50)

Set @Name2 = 'Data'

Set @Name2 = '%' + @Name2 + '%'

Select * from #main A Join #dept B ON A.DepId=B.id Where A.Name Like @Name2 Or B.Title LIke @Name2






Re: SP - Strange problem

Gurpreet Singh Gill

Does

Code Snippet

SELECT distinct tblPerson.ID, tblPerson.Name, tblPerson.Surname

FROM tblPerson INNER JOIN tblPersonMeta ON tblPerson.ID = tblPersonMeta.PersonID

WHERE (tblPerson.Comments like @Name) and Active = 0

returns the desired result

Gurpreet S. GIll






Re: SP - Strange problem

Tovdb

That worked.. you're amazing.. thanks!!

Can you tell me why this causes problems

(edit).. This is getting more complicated.. Smile.. now when I search for either tblPersonMeta field, it workes fine... however when I search for the name of a person.. it doesn't find him...or her...

Should I change every nchar type to nvarchar in the db





Re: SP - Strange problem

Chris Howarth

CHAR and NCHAR datatypes are often used where the data that the column is to hold is of a fixed length, e.g. credit card numbers, National Insurance numbers.

If the data that you will be storing is of variable length then NVARCHAR might be a superior option. If the lengths of the strings that you will be storing will frequently be below the maximum length defined for the column then one advantage is that you'll reduce the amount of disk space that your data will consume, another is that you'll be able to fit more rows into a SQL Server 'page' and you should, therefore, see performance benefits as a result as fewer data pages will have to be read to satisfy your SQL queries.

Have a look here for more in-depth information on the pros and cons of each option:

http://sqljunkies.com/WebLog/odds_and_ends/archive/2005/09/12/16725.aspx

Chris






Re: SP - Strange problem

Tovdb

Thanks for the background info..

But it doesn't explain why after changing the type I can't search for Name of Surname anymore and to make it even more interesting, I can find one specific person in the table .. looking for anyone else will turn up no results. Funny enough that person is me. I really didn't program that in.





Re: SP - Strange problem

Chris Howarth

You'll find that this is related to the padding spaces present in CHAR and NCHAR datatypes.

Without seeing your data it's difficult to prove exactly what's happening in your particular case, but the following examples should help you to see what's going on:

DECLARE @Name NCHAR(50)

SET @Name = 'Data'

SET @Name = '%' + @Name + '%'

SELECT @Name

GO

DECLARE @Name NVARCHAR(50)

SET @Name = 'Data'

SET @Name = '%' + @Name + '%'

SELECT @Name

GO

Produces this output:

--First query

'%Data '

--Second query

'%Data%'

So you can see that when using NCHAR for your input parameter (first query) then your data must contain the word 'Data' followed by 45 trailing spaces in order to match the criteria specified. Note that the second percentage mark is absent due to the way in which the percentage mark is appended (and then implicitly removed due to truncation after 50 characters) to the NCHAR string.

If you are setting the value of the @Name parameter from code and then specifying the datatype then you'll probably end up with this being passed into your stored proc:

'%Data% '

In this case your data must contain the word 'Data' followed by at least 44 trailing spaces.

As I said, it's difficult to be more precise without seeing your data, but hopefully this will give you an insight as to the pitfalls of using the LIKE operator with the NCHAR datatype.

Chris






Re: SP - Strange problem

Tovdb

But I've now changed every parameter type to nvarchar so what you are saying shouldn't cause anymore problems.. should it



Re: SP - Strange problem

Chris Howarth

I wouldn't have thought so.

It might be worth setting up SQL Profiler to capture the values of the parameters passed in to your stored proc when executed via your application. If you could post the contents of the 'TextData' column and your stored proc's full definition back to the forum then this would be helpful.

In case you're not familiar with SQL Profiler then here are two links which will show you the basics involved to allow you to capture the correct data. The first link is for the version of Profiler shipped with SQL Server 2000, the second for the version shipped with SQL Server 2005:

http://www.developer.com/db/article.php/3482216

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1159433,00.html bucket=ETA&topic=301323

Chris






Re: SP - Strange problem

Tovdb

That would be the following:

Code Snippet
exec getRelationsByAll @Name=N'%onno%'

And tblPerson contains a person with the name "Onno Baudouin" so should find him.. however he doesn't appear.

Then again if I search :

Code Snippet

exec getPersonsByAll @Name=N'%to%'

It finds me... Tobias Vandenbempt

I'd love to see some logic in this .. Smile