troubleshooting Question

Passing a parameter with Like

Avatar of skull52
skull52Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
8 Comments1 Solution448 ViewsLast Modified:
I have a query that displays voided transactions, and I am passing a parameter value to the WHERE clause with like but it is not returning any rows, I know I am missing something but I can't seem to figure out what.

declare @ITEM char(1000)
set @ITEM = 'ATIGM9130'
SELECT 
[Company]='NAME',
[Source]=cast('History' as varchar(7)),
[Doc_Type]=CASE SOP30200.SOPTYPE when 1 then 'QUOTE' when 2 then 'ORDER' when 3 then 'INVOICE' when 4 then 'RETURN' when 5 then 'BACKORDER' else '' end,
[Doc_Number]=SOP30200.SOPNUMBE,
SOP30300.ITEMNMBR,
SOP30300.ITEMDESC,
SOP30300.QUANTITY,
SOP30200.ORIGNUMB,
[Doc_Date]=SOP30200.DOCDATE,
[GL_Posting_Date]=SOP30200.GLPOSTDT, 
[Invoice_Date]=SOP30200.INVODATE,
SOP30200.FUFILDAT,
SOP30200.ACTLSHIP,
SOP30200.LOCNCODE,
SOP30200.BCHSOURC,
[Batch_No]=SOP30200.BACHNUMB,
SOP30200.CUSTNMBR, 
SOP30200.CUSTNAME,
SOP30200.ADDRESS1,
SOP30200.ADDRESS2,
SOP30200.CITY,
SOP30200.[STATE],
SOP30200.CNTCPRSN,
SOP30200.PHNUMBR1, 
SOP30200.PHNUMBR2,
SOP30200.SHIPMTHD,
[Posting_Status]=CASE SOP30200.PSTGSTUS when 0 then 'NOT POSTED' when 2 then 'POSTED' else 'UNKNOWN' end,
[Void_Status]=CASE SOP30200.VOIDSTTS when 1 then 'VOIDED' else '' end,
--[Message] = CASE SOP30200.VOIDSTTS when 1 then 'Voided By will be the Batch_No for the transaction.' else '' end,
[Voided By]= CASE SOP30200.VOIDSTTS when 0 then '' else SOP30200.BACHNUMB end,
IV00101.ITMCLSCD
FROM         SOP30200 WITH(nolock) INNER JOIN
                      SOP30300 WITH(nolock)  ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                      IV00101 WITH(nolock)  ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (IV00101.ITMTRKOP = 2) AND (NOT (SOP30200.SOPTYPE = 1)) AND (SOP30300.ITEMNMBR  LIKE '%'+@ITEM+'%' )--AND SOP30300.ITEMNMBR in (select Value from dbo.parmstolist(@ITEM, ','))
ORDER BY 8 DESC
ASKER CERTIFIED SOLUTION
Habib Pourfard
Senior Software Engineer
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros