Solved

Using LIKE in stored proc parameter

Posted on 2008-06-23
5
264 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
Comment Utility
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
Comment Utility
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
Comment Utility
I'll make this change and see if it helps...
0
 

Author Comment

by:reb1982
Comment Utility
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
Comment Utility
You get the points!  

Thanks again!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now