Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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?
0
grwallace
Asked:
grwallace
  • 3
1 Solution
 
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
 
Paul_Harris_FusionCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now