?
Solved

Need to use parameter in like clause

Posted on 2010-01-01
7
Medium Priority
?
267 Views
Last Modified: 2012-05-08
Hi

I need to use a parameter in a like clause. This will become a stored procedure. How do I do this?

DECLARE @LoginID varchar(30)
DECLARE @LastName varchar(4)

SET @LoginID='LI123'
SET @LastName='smith'

SELECT      
                Attorneys.LoginID,
      ClientInformation.FirstName First,
      ClientInformation.LastName,
      ClientInformation.CompletedDate,
      ClientInformation.Certified

FROM      ClientInformation
          INNER JOIN Attorneys
            ON ClientInformation.AccessCode = Attorneys.AccessCode
WHERE      
     LoginID=@LoginID and
     ClientInformation.LastName LIKE @LastName

0
Comment
Question by:Charles Baldo
6 Comments
 
LVL 12

Accepted Solution

by:
geowrian earned 1000 total points
ID: 26160669
What error are you getting?
0
 

Author Comment

by:Charles Baldo
ID: 26160731
No Error Just no Data
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 400 total points
ID: 26161431
Try using:

DECLARE @LoginID varchar(30)
DECLARE @LastName varchar(4)

SET @LoginID='LI123'
SET @LastName='smith'

SELECT      
                Attorneys.LoginID,
      ClientInformation.FirstName First,
      ClientInformation.LastName,
      ClientInformation.CompletedDate,
      ClientInformation.Certified

FROM      ClientInformation
          INNER JOIN Attorneys
            ON ClientInformation.AccessCode = Attorneys.AccessCode
WHERE      
     LoginID=@LoginID and
     ClientInformation.LastName LIKE '%' + @LastName + '%'
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 4

Assisted Solution

by:daletian
daletian earned 600 total points
ID: 26161690
pass  '%' character in you string to the variable

eg

DECLARE @LoginID varchar(30)
DECLARE @LastName varchar(4)

SET @LoginID='LI123'
SET @LastName='%smith%'

SELECT      
                Attorneys.LoginID,
      ClientInformation.FirstName First,
      ClientInformation.LastName,
      ClientInformation.CompletedDate,
      ClientInformation.Certified

FROM      ClientInformation
          INNER JOIN Attorneys
            ON ClientInformation.AccessCode = Attorneys.AccessCode
WHERE      
     LoginID=@LoginID and
     ClientInformation.LastName LIKE @LastName
0
 

Author Closing Comment

by:Charles Baldo
ID: 31671917
Both of the coding solutions worked. It was the question by geowrian that me me realize what the issue was.  I was not getting an error I was getting nop data. It was a logic error of the DECLARE @LastName varchar(4) 4 bytes was not enough. Once I fixed that my code worked.
0
 

Author Comment

by:Charles Baldo
ID: 26161725
Both of the coding solutions worked. It was the question by geowrian that me me realize what the issue was.  I was not getting an error I was getting nop data. It was a logic error of the DECLARE @LastName varchar(4) 4 bytes was not enough. Once I fixed that my code worked.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

862 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