Writing a search query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Writing a search query

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 11:18:49

I wonder if u could help me with this query.

I have a SP:

CREATE PROCEDURE Orders_GetAdminOrders
@Search nvarchar (50),
@CompanyID int AS

SELECT * FROM Orders o join OrderLine d
on
o.OrderID = d.OrderID
where
( o.orderid = @Search OR o.username like '%'+@Search+'%' OR o.paymentoption like '%@'+@Search+'%')

and o.CompanyID = @CompanyID

Now when I run this as: exec Orders_GetAdminOrders '17',1
(searching orderID '17') the query runs fine, and I get the result.

But now I want to search for a username, so: exec Orders_GetAdminOrders 'q8',1
But this time, I get the following error:
Syntax error converting the nvarchar value 'q8' to a column of data type int.

I did further experiements. If I remove orderid from my search query, so I am only left with the following query:
(o.username like '%'+@Search+'%' OR o.paymentoption like '%@'+@Search+'%')

Now if I run: exec Orders_GetAdminOrders 'q8',1
The search is successful.

Now if I try to search something in the paymentoption column, the search is unsuccessful.

What seems to be happening is that my SP seems to be only searching in the first column specified in the SP.

How can I write this query so all of the columns are searched???

thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-30 : 11:51:05

Is Orders.OrderId defined as integer?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-30 : 11:55:34

You are comparing INT with VARCHAR input.

declare @search varchar(100)
set @search = 'hasana'

declare @orders table (orderID int, username varchar(25))
insert into @orders
	select 1, 'Nathan' union all select '2', 'hasanali00'

select *
from @orders 
where cast(orderID as varchar) = @search 
	or username like '%' + @search + '%'

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 11:55:50

yes, its defined as int

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 12:20:46

In my table, I have: orderid=int , username=nvarchar, paymentoption=nvarchar

So now I have changed my query to:
cast (o.orderid as nvarchar) = @Search OR cast ( o.username as nvarchar) like '%'+@Search+'%' OR cast( o.paymentoption as nvarchar) like '%@'+@Search+'%')

Basically, I have added Cast(.....as nvarchar) in front of all the columns.

Now I can search in orderid, and username.

But still I cannot search in the paymentoption column.

What could be the reason??
regards

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-30 : 12:29:24

Typo?

quote:
Originally posted by hasanali00

In my table, I have: orderid=int , username=nvarchar, paymentoption=nvarchar

So now I have changed my query to:
cast (o.orderid as nvarchar) = @Search OR cast ( o.username as nvarchar) like '%'+@Search+'%' OR cast( o.paymentoption as nvarchar) like '%@'+@Search+'%')

Basically, I have added Cast(.....as nvarchar) in front of all the columns.

Now I can search in orderid, and username.

But still I cannot search in the paymentoption column.

What could be the reason??
regards

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 15:45:46

quote:
Originally posted by nathans

Typo?

quote:
[i] OR cast( o.paymentoption as nvarchar) like '%@'+@Search+'%')

Basically, I have added Cast(.....as nvarchar) in front of all the columns.

All I can say: What an eye sight

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources