Using LIKE with variables in a SP

Question

Using LIKE with variables in a SP

Rich Ray

SSC Eights!

Points: 887
More actions
October 7, 2008 at 9:04 am

#191895

Hi All,
I have created a stored procedure which takes two input parameters. I need the query within the SP to do a search based on results LIKE the values the user enters.
I have DisplayNo and Desc as my variables which I have defined:
DECLARE @DisplayNo nvarchar(20)
DECLARE @Desc nvarchar (60)
I have then (as a test) checked that the value I pass in is getting converted correctly so I can use it with LIKE...
SET @DisplayNo = 'CNST'
SET @DisplayNo = '''%' + @DisplayNo + '%''';
SET @Desc = '''%' + @Desc + '%''';
SELECT @DisplayNo
The output was '%CNST%'
In my query within the SP I have the following WHERE clause:
WHERE
srl.QuantityReceipted > 0 AND
(sr.DisplayNo LIKE @DisplayNo OR sri.Description LIKE @DESC)
But it brings back no results at all. If I just enter '%CNST%' in place of @DisplayNo then it works.
I know its probably simple but its driving me mad.
Any help would be much appreciated!
Thanks,
Richard

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Change this:

SET @DisplayNo = 'CNST'
SET @DisplayNo = '''%' + @DisplayNo + '%''';
SET @Desc = '''%' + @Desc + '%''';
SELECT @DisplayNo

to this:

SET @DisplayNo = 'CNST'
SET @DisplayNo = '%' + @DisplayNo + '%';
SET @Desc = '%' + @Desc + '%';
SELECT @DisplayNo

And give that a try.

😎

Rich Ray SSC Eights! Points: 887 More actions · Answer 2

Weird - I had it like that before but it wasn't working. Have been messing around with it so much I didn't think to revert back to it!

Thanks 🙂

While I'm at it.....how should I deal with blank blank values being passed to either variable because at the moment it will bring back all results regardless as it is going to be passing for example:

WHERE

srl.QuantityReceipted > 0 AND

(sr.DisplayNo LIKE '%CNST%' OR sri.Description LIKE '%%')

Garadin One Orange Chip Points: 29613 More actions · Answer 3

That depends on what you'd like to happen in the case that they don't supply either value.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Rich Ray SSC Eights! Points: 887 More actions · Answer 4

If nothing is passed to it then I do not want it to search for everything, i.e. '%%'

So if they only entered a DocumentNo value but no Desc value then I only want it to bring back records where the DocumentNo matches (well, is LIKE).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

At this point, I'd like to to point you in another direction for a moment. Please take the time to read this article, http://www.sqlservercentral.com/articles/Best+Practices/61537/.

Follow the guidelines in it and we can provide you much better help. At the moment, I really can't help you much more as i don't have enough information to know what you are trying to accomplish.

😎

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Declare your passed parameters as

@DisplayNo AS varchar(20)= ' '

Add IF tests to your procedure for example:

IF LEN(LTRIM(RTRIM@Display))) = 0

BEGIN

SELECT @DisplayNo

WHERE

srl.QuantityReceipted > 0 AND

(sri.Description LIKE '%@Desc%')

RETURN

END

another IF for a blank @Desc value, and yet a 3rd when both values are passed.

Of course there are many, many ways to accomplish what you want, can you be more specific in what you desire to achieve.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Rich Ray SSC Eights! Points: 887 More actions · Answer 7

Thanks BigBucket - you set me on the right path.

I just put an IF statement in as follows:

IF @DisplayNo = '%%'

SET @DisplayNo = ''

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 8

Why not just do this inline?

WHERE

srl.QuantityReceipted > 0 AND

(sr.DisplayNo LIKE ('%' + COALESCE(@DisplayNo,'') + '%') OR sri.Description LIKE ('%' + COALESCE(@Desc, '') + '%'))

Gary Johnson
Sr Database Engineer