March 26, 2002 at 8:02 am
Hi everybody!
I have here a weird problem.
The important line from my stored procedure (sp_GetAllPart_Number_Search) looks like this:
select Part_Number from Orderitem where PO_Number = @PO_Number and PO_Line_Description like @Description order by Line_Number
in query Analyzer the call to the stored procedure look like this:
EXEC sp_GetAllPart_Number_Search '100124','%CAP%'
And it doesn't return any record
but if I run like this in Query Analyzer:
select Part_Number from Orderitem where PO_Number = '100124' and PO_Line_Description like '%CAP%' order by Line_Number
it return the records that I want.
What is the prblem?? the variables are declared like this in the stored procedure:
@PO_Number char(7),
@Description char(20)
Thank you,
Duruguru
March 26, 2002 at 8:33 am
Had a similar problem. Solved it like this:
create table OrderItem
( PO_Number char(7)
, Part_Number char(2)
, PO_Line_Description char( 20)
, Line_Number int
)
go
insert OrderItem select '1010101', 'AA' , 'Capital Expenditure', 1
insert OrderItem select '100124', 'BB', 'Capital', 1
insert OrderItem select '100124', 'CC', 'Expense', 2
select *
from OrderItem
select Part_Number
from Orderitem
where PO_Number = '100124'
and PO_Line_Description like '%CAP%'
order by Line_Number
alter procedure sp_GetAllPart_Number_Search
@PO_Number char(7)
, @Description char(20)
as
declare @search varchar(40)
select @search = rtrim( @description) + '%'
select @PO_Number, @Description
select Part_Number
from Orderitem
where PO_Number = @PO_Number
and PO_Line_Description like @search
order by Line_Number
return
EXEC sp_GetAllPart_Number_Search '100124', 'capital'
EXEC sp_GetAllPart_Number_Search '100124', 'capi'
drop procedure sp_GetAllPart_Number_Search
drop table OrderItem
Not sure why this is an issue, but it is.
SQL 2000, SP1 here
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 26, 2002 at 8:50 am
Thank you Steve.
You are great! I am running SQL7, hoping to upgrade in the near future to SQL2000 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply