Post reply

Variable in Like Statement

  • April 9, 2008 at 8:56 am

    #94004

    I have a form that has two textboxes. Those values are passed to a 2nd page that has a gridview. The gridview uses a stored procedure as its source. The 2 values are passed to the SP. Those values are put into two variables called @varStreetAddress and @varStreetName. I want to use the @varStreetName in a Like Statment. Here is part of the code that I have right now:

    ALTER PROCEDURE [dbo].[usp_Get_Street_Info]

    -- Add the parameters for the stored procedure here

    @streetaddress varchar(50) ,

    @streetname varchar(50)

    AS

    declare @varStreetAddress varchar(50)

    declare @varStreetAddress2 varchar(50)

    declare @varStreetName varchar(50)

    SET @varStreetAddress = '%' + ISNULL(@streetaddress,'') + '%'

    Set @varStreetAddress2 = ISNULL(@streetaddress,'')

    SET @varStreetName = '%' + ISNULL(@streetName,'') + '%'

    .

    .

    .

    where

    (Cast(@varStreetAddress2 as int) > cast(snm.street_range_low as int)

    and Cast(@varStreetAddress2 as int) < cast(snm.street_range_high as int))

    and snm.street_name like @varStreetName

    when I run it, it doesn't really act correctly. I am searching for the street name Capital. I get several records with the name Capital in it. But I do not get the name Capital by itself. If I run a select statement using LIKE '%Capital%' it works fine.

    Any suggestions?

  • April 9, 2008 at 10:39 am

    #800538

    Can you show some examples of how you're calling usp_Get_Street_Info and which ones do and don't work?

    From what you've provided, if you passed in a NULL for [streetaddress], or one that's not within your high-low range, the WHERE clause will never be met regardless of how the [streetname] is passed in.

Viewing 2 posts - 1 through 1 (of 1 total)

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