Using LIKE in a SQL Server stored procedure

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?
grwallaceAsked:
Who is Participating?
 
Paul_Harris_FusionConnect With a Mentor Commented:
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
 
Paul_Harris_FusionCommented:
what about


WHERE [Product Code] LIKE '+' + @PartNumber + '+' AND StbBranchId = @Branch
0
 
Paul_Harris_FusionCommented:
Sorry - meant to type this  

WHERE [Product Code] LIKE '%' + @PartNumber + '%' AND StbBranchId = @Branch
0
 
grwallaceAuthor Commented:
Afraid that doesn't work either, though I can't see why not!!
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.