SQL2005 Select where a variable contents is like ...

I have a SQL statement where I'm trying to return selected data based on a customer number.

Since I want to look for either the fule or partial number I'm trying to use a like instaed of equal.

If I use the literal custnmbr in the below select it workse file but if I try using a variable it doesn't return anything.

I'm sure I have the like with a variable formatted incorrectly but not sure how to correct.

Thanks

      
Declare
      @KLEOrderNumber      VARCHAR(30),
      @PONumber                  VARCHAR(30),
      @AltPONumber            VARCHAR(61),
      @AcctNumb1                  varchar(15),
      @AcctNumb2                  varchar(15),
      @BeginDate                  DATETIME,
      @EndDate                  DATETIME
      
set @KLEOrderNumber = ''
set             @PONumber = ''
set            @AltPONumber = ''
set            @AcctNumb1 = '17319'
set            @AcctNumb2 = '344122'
set            @BeginDate = '1/1/2012'
set            @EndDate = '4/11/2013'      
      
      
      IF LEN(RTRIM(LTRIM(@KLEOrderNumber))) = 0
            SET @KLEOrderNumber = NULL      
      IF LEN(RTRIM(LTRIM(@PONumber))) = 0
            SET @PONumber = NULL      
      IF LEN(RTRIM(LTRIM(@AltPONumber))) = 0
            SET @AltPONumber = NULL      
      IF LEN(RTRIM(LTRIM(@AcctNumb1))) = 0
            SET @AcctNumb1 = NULL
      IF LEN(RTRIM(LTRIM(@AcctNumb2))) = 0
            SET @AcctNumb2 = NULL
      IF (LEN(RTRIM(LTRIM(@BeginDate))) = 0) OR (@BeginDate = '1900-01-01') OR (@BeginDate IS NULL)
            SET @BeginDate = GETDATE()-365      
      IF (LEN(RTRIM(LTRIM(@EndDate))) = 0) OR (@EndDate = '1900-01-01') OR (@EndDate IS NULL)
            SET @EndDate = GETDATE()
      
      
      
      
      SELECT --TOP 500
         A.CSTPONBR [PONumber]
       , A.SOPNUMBE [KLEOrderNumber]
       , A.Address3 [AltPONumber]
       , B.SOPTYPE
       , D.TCC [SKU]
       , CAST(B.UNITPRCE AS DECIMAL(15,2)) [UNITPRCE]
       , CAST(B.XTNDPRCE AS DECIMAL(15,2)) [XTNDPRCE]
       , CAST(B.QUANTITY AS INT) [QuantityOrdered]
       , CAST(B.QTYFULFI AS INT) [QuantityShipped]
       , CAST(B.QTYPRINV AS INT) [InvoicedQuantityShipped]
       , CONVERT(VARCHAR(10),A.DOCDATE,101) [OrderDate]
       , CONVERT(VARCHAR(10),B.ReqShipDate,101) [ShipDate]
       , CONVERT(VARCHAR(10),B.ReqShipDate,101) [FutureShipDate]
       , a.CUSTNMBR [Account]
      FROM
       KG.dbo.TEAMVIEW_SOP10100_SOP30200 A WITH(NOLOCK,READUNCOMMITTED)
       INNER JOIN
       KG.dbo.TEAMVIEW_SOP10200_SOP30300 B WITH(NOLOCK,READUNCOMMITTED)
            ON B.SOPNUMBE = A.SOPNUMBE AND
               B.SOPTYPE  = A.SOPTYPE  AND
               A.SOPNUMBE = ISNULL(@KLEOrderNumber,A.SOPNUMBE) AND
               A.SOPTYPE  = 2 AND
               A.DOCDATE BETWEEN @BeginDate AND @EndDate AND
               (A.CUSTNAME LIKE 'SBA-%'
                        or a.CUSTNAME like '%SCHN%')
       INNER JOIN
       KG.dbo.SOP10106 C WITH(NOLOCK,READUNCOMMITTED)
            ON C.SOPNUMBE = A.SOPNUMBE AND
               C.SOPTYPE  = A.SOPTYPE AND
               --A.CSTPONBR = ISNULL(@PONumber,A.CSTPONBR)AND
               A.CSTPONBR like (case when @PoNumber is NULL
                        then ISNULL(@PONumber,A.CSTPONBR)
                        else '%'+@PONumber+'%' end) AND
               --A.Address3 = ISNULL(@AltPONumber,A.Address3)
               A.Address3 like (case when @AltPoNumber is NULL
                        then ISNULL(@AltPONumber,A.Address3)
                        else '%'+@AltPONumber+'%' end)
       INNER JOIN KG.dbo.IV00101 D WITH(NOLOCK,READUNCOMMITTED)
            ON D.ITEMNMBR = B.ITEMNMBR
       INNER JOIN KG.dbo.RM00101 R WITH(NOLOCK,READUNCOMMITTED)
            ON R.CUSTNMBR = A.CUSTNMBR
                  and R.salsterr like '%SBA%'
       INNER JOIN KG.dbo.HM00600 E WITH(NOLOCK,READUNCOMMITTED)
            ON E.CUSTNMBR = A.CUSTNMBR

      
      --where a.custnmbr like '344122%'
      where a.custnmbr like '@AcctNumb2'+'%'
jdr0606Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
where a.custnmbr like @AcctNumb2+'%'
0
 
jdr0606Author Commented:
Thanks!

Duh!  Sometimes we can't see the forest for the trees
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.