Variable in Like Statement

• Chris Evelo

  SSC Enthusiast

  Points: 182

  More actions

  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?

• Todd Engen

  SSCrazy Eights

  Points: 9869

  More actions

  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.