Using LIKE in a SQL Server stored procedure

Posted on 2013-10-25
Medium Priority
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?
Question by:grwallace
  • 3
LVL 12

Expert Comment

ID: 39599946
what about

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

Expert Comment

ID: 39599950
Sorry - meant to type this  

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

Author Comment

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

Accepted Solution

Paul_Harris_Fusion earned 1500 total points
ID: 39600009
I just tried a very simple example in my own database by creating procedure TestProc which contains line:

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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

619 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