SQL 2005 - Search String

Question

I want to write a stored procedure that will accept a parameter of @searchString. This will be a varchar(100) and will contain a query value. How can I write the sp so that it could do something like:

SELECT *
FROM Application a
INNER JOIN Applicant app ON app.ApplicationId = a.ApplicationId
WHERE a.ApplicationId = @searchString
OR app.Name like '@searchString%'
OR app.PostCode like '@searchString%'

The problem for me is how to handle the fact that the searchString may contain an Id which will be an int or could be a string value.

Answer 1

You could put the select together as a string with proper quoting, and then use sp_executesql to run it.

But be aware that there is potential for SQL injection with this!

Answer 2

You can check that searchString has only numbers by using PATINDEX and then only compare with applicationId

SELECT *
FROM Application a
INNER JOIN Applicant app ON app.ApplicationId = a.ApplicationId
WHERE (PATINDEX('%[^0-9]%', @searchString) = 0 AND a.ApplicationId = @searchString)
OR app.Name like @searchString + '%'
OR app.PostCode like '@searchString + '%'

Answer 3

I solved this by creating another local variable and if the searchString was numeric, convert it and if not then set to zero (an id I know won't exist).

declare @id int
if ISNUMERIC(@searchString) = 1
    set @id = CONVERT(int, @searchString)
else
    set @id = 0

Then where clause looks like:

WHERE
    a.ApplicationId = @id
    OR app.Surname like @searchString + '%'
    OR app.Forenames like '%' + @searchString + '%'

Answer 4

What about passing the stored proc an XML input parameter? That way when you "unpack" the XML you'd be able to control the data types and handle IDs or text as appropriate.

Answer 5

SELECT *
FROM Application a
INNER JOIN Applicant app ON app.ApplicationId = a.ApplicationId
WHERE a.ApplicationId = 
  case when ISNUMERIC(@searchString)
  then
    CONVERT(int, @searchString)
  else
    0
  end
OR app.Name like @searchString + '%'
OR app.PostCode like @searchString + '%'