Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using LIKE in stored proc parameter

Posted on 2008-06-23
5
290 Views
Last Modified: 2010-04-21
I have a dynamic query that I made into a stored procedure using parameters in SQL Server 2005...

SELECT count(*) as [Count]
  FROM [Orders] o
left join [order_detail] od
on o.Order_ID = od.Order_ID
where o.sold_to_customer_PO_ref like @OrderNumber + '%' and od.SKU = @SKU and (o.Order_Status = 'Hold' or o.Order_Status = 'OKAY')and o.carrier_code <> 'CANCEL'

So, here is my problem, I pass in the order number and sku when I execute the proc, like so:

EXEC sp_GetOrderCount '3200112', '883575550329'

But the procedure returns a count of 0.  When I just run the query (like listed above), I get an accurate count of 4.

What's wrong with my proc?
Thanks in advance!
0
Comment
Question by:reb1982
  • 4
5 Comments
 

Author Comment

by:reb1982
ID: 21850259
Here is what I used to create the procedure:

CREATE procedure [sp_GetOrderCount] @OrderNumber char(17), @SKU char (15) AS

SELECT count(*) as [Count]
  FROM [Orders] o
left join [order_detail] od
on o.Order_ID = od.Order_ID
where o.sold_to_customer_PO_ref like @OrderNumber + '%' and od.SKU = @SKU and (o.Order_Status = 'Hold' or o.Order_Status = 'OKAY')and o.carrier_code <> 'CANCEL'

0
 
LVL 8

Accepted Solution

by:
Chumad earned 500 total points
ID: 21850366
Reb:

You have your orderNumber defined as a char. By definition, char will ALWAYS have the number of characters you define. So in your case, you pass in '3200112' - what SQL is seeing is : '3200112      ' with a bunch of spaces appended to the end of it.

If this is not intended, you may try changing to varchar instead of char.
CREATE procedure [sp_GetOrderCount] @OrderNumber varchar(17), @SKU varchar (15) AS

Open in new window

0
 

Author Comment

by:reb1982
ID: 21850443
I'll make this change and see if it helps...
0
 

Author Comment

by:reb1982
ID: 21850628
I changed the data type, as you suggested and also added % to both the beginning and end of the order number variable.

Not sure which did the trick, but it seems to be working now!

Thanks so much for your quick response!
0
 

Author Closing Comment

by:reb1982
ID: 31469942
You get the points!  

Thanks again!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question