Solved

Using LIKE in a SQL Server stored procedure

Posted on 2013-10-25
4
207 Views
Last Modified: 2013-10-25
I have a stored procedure which works perfectly when an = condition is used = e.g.

WHERE [Product Code] = @PartNumber AND StbBranchId = @Branch

In order to do some more advanced manual selection in the application I wanted to use
WHERE [Product Code] LIKE @PartNumber AND StbBranchId = @Branch

and for the user to enter something like A1% in the parameter string

Now in a normal query if I say:-
WHERE     (stmas.[Product Code] LIKE 'a1%') AND (StBranchDets.StbBranchId = 1)

it works perfectly, but not in the SP

I have even tried:-

WHERE [Product Code] LIKE '+@PartNumber+' AND StbBranchId = @Branch

but no difference.

Any ideas anyone?
0
Comment
Question by:grwallace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 39599946
what about


WHERE [Product Code] LIKE '+' + @PartNumber + '+' AND StbBranchId = @Branch
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 39599950
Sorry - meant to type this  

WHERE [Product Code] LIKE '%' + @PartNumber + '%' AND StbBranchId = @Branch
0
 

Author Comment

by:grwallace
ID: 39599975
Afraid that doesn't work either, though I can't see why not!!
0
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 500 total points
ID: 39600009
I just tried a very simple example in my own database by creating procedure TestProc which contains line:
    SELECT * FROM SUMMARY where CLASS LIKE @P1

and if I execute my proc as follows:
    exec TestProc 'BUS%'

then I get all rows from table SUMMARY where CLASS like 'BUS%'

So what you are trying works fine in my database.

Sorry I can't be more help.
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

697 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