Like keyword problem in Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Like keyword problem in Stored procedure

Author

Topic

gxs8938
Starting Member

23 Posts

Posted - 2010-01-18 : 17:51:02

Hello all,

I have a stored procedure like below
Alter PROCEDURE [dbo].[ContactsListBySearch]
@AuthorID int,
@currentPage INT,
@pageSize INT,
@searchStr nvarchar

AS
BEGIN
set nocount on;

WITH tempLog AS (
SELECT distinct ROW_NUMBER()OVER (ORDER BY email DESC) AS Row,

email,username from AddContact where userid = @AuthorID and email like '%'+@searchStr+'%' and username like '%'+@searchStr+'%' )

SELECT email,username
FROM tempLog
WHERE Row between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize)

END
-----------------------------------------------------------------
for a search string david it gives me unrelated rows.

But for the same query string david if i run the query
select email,username from addcontact where userid=2 and email like '%david%' and username like '%david%'

It gives me exact result.

How to pass an paramter as a string in the stored procedure?? please help me.
regards,
Guru

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-18 : 22:06:49

I stared at your query for 5 minutes, and couldn't figure out why it works when you run the query directly. Only things that come to mind are a)the use of the DISTINCT key word in the stored proc and, b) whether the collation is case-sensitive and perhaps you are passing in the parameters with capitalizations.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-18 : 22:14:53

[code]
Alter PROCEDURE [dbo].[ContactsListBySearch]
@AuthorID int,
@currentPage INT,
@pageSize INT,
@searchStr nvarchar (100)
[/code]

If you don't specify the size, the default is 1

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-19 : 00:20:02

quote:
Originally posted by khtan

Alter PROCEDURE [dbo].[ContactsListBySearch]
@AuthorID int,
@currentPage INT,
@pageSize INT,
@searchStr nvarchar (100)

If you don't specify the size, the default is 1

KH
[spoiler]Time is always against us[/spoiler]

Good catch

It is not neccessarily default value 1 for unspecified length. It varies depends on where you use it
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

gxs8938
Starting Member

23 Posts

Posted - 2010-01-19 : 01:04:08

Thanks Guys,

Indeed it was an good catch. I went crazy figuring out the problem. Fresh head helped :)

--Guru

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-19 : 07:41:46

khtan, great catch!! :)

In the past, I have been burned by a similar problem from the C# side when trying to access SQL database. These days, I tend to use varchar(max) out of fear, but that does not seem like a good practice either and almost feels like cheating.

[makes a mental note to herself to look up how SQL stores varchar(max)]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources