Post reply

Using LIKE with variables in a SP

  • 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

  • October 7, 2008 at 9:17 am

    #881747

    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.

    😎

  • October 7, 2008 at 9:20 am

    #881750

    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 '%%')

  • October 7, 2008 at 10:03 am

    #881783

    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]

  • October 7, 2008 at 10:05 am

    #881786

    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).

  • October 7, 2008 at 10:18 am

    #881796

    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://qa.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.

    😎

  • October 7, 2008 at 10:21 am

    #881798

    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]

  • October 8, 2008 at 12:09 pm

    #882498

    Thanks BigBucket - you set me on the right path.

    I just put an IF statement in as follows:

    IF @DisplayNo = '%%'

    SET @DisplayNo = ''

  • October 8, 2008 at 1:25 pm

    #882541

    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

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

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