Stored Procedure WHERE LIKE ID or NAME

Question

Group, I am trying to create a stored procedure using one variable @Customer. What I want to do is put something in my WHERE clause that says if it is a number search the CustomerID field where the number entered is LIKE CustomerID... If a char is entered search the CustomerName field where the text entered is LIKE CustomerName. Below is an example of what I am trying to do:

CREATE PROCEDURE [dbo].[uspGetCustomer] (@Customer VARCHAR(100))

AS

SET NOCOUNT ON

SELECT * 
FROM dbo.Customers
WHERE CASE WHEN ISNUMERIC(@Customer) THEN CustomerID LIKE @Customer + '%'
ELSE CustomerName LIKE @Customer + '%' END

Any suggestions?

Answer 1

SELECT  * 
FROM    dbo.Customers
WHERE   ISNUMERIC(@Customer) = 1
        AND CustomerID = CAST(@Customer AS INTEGER)
UNION ALL
SELECT  *
FROM    dbo.Customers
WHERE   NOT ISNUMERIC(@Customer) = 1
        AND CustomerName LIKE @Customer + '%'

This will use the approproate indexes on CustomerID and CustomerName

Answer 2

It could just be me, but using a single variable to represent two different fields gives me the bad-practice willies. I would rewrite this stored procedure to take in two different, nullable variables (one int, CustomerID, and one varchar, CustomerName). It would look like this:

CREATE PROCEDURE [dbo].[uspGetCustomer] (
@CustomerID int = null, 
@CustomerName VARCHAR(100) = null)

AS

IF @CustomerID IS NOT NULL BEGIN
     SELECT * FROM Customers WHERE CustomerID = @CustomerID
END ELSE IF @CustomerName IS NOT NULL BEGIN
     SELECT * FROM Customers WHERE CustomerName LIKE @CustomerName
END ELSE
     --error handling, return empty set maybe?
END

If this simply isn't an option, then you could still use:

CREATE PROCEDURE [dbo].[uspGetCustomer] (@Customer VARCHAR(100))

AS

DECLARE @NameMatch;

IF ISNUMERIC(@Customer) = 1 BEGIN
     SELECT * FROM Customers WHERE CustomerID = CAST (@Customer AS int)
END ELSE BEGIN
     SET @NameMatch = '%' + @Customer + '%'
     SELECT * FROM Customers WHERE CustomerName LIKE @NameMatch
END

Answer 3

Keep it simple, have an index on each, determine on the front-end which one it is and set the correct parameter.

CREATE PROCEDURE dbo.getCustomer 
(@CustomerID int = null, 
@CustomerName VARCHAR(100) = null)
AS
SET NOCOUNT ON

SELECT * 
FROM Customers 
WHERE CustomerID = @CustomerID
or CustomerName like @CustomerName + '%'

Answer 4

I'd do it using an IF statement, since putting that logic in the WHERE makes it kind of hard to read later on:

DECLARE @match = @CustomerID + '%'

IF ISNUMERIC(@CustomerID) = 1 
BEGIN
     SELECT * FROM CUSTOMERS WHERE CustomerID LIKE @match
END ELSE BEGIN
     SELECT * FROM CUSTOMERS WHERE CustomerNAME LIKE @match
END  

update: I'm wondering if the CustomerID field is an INT. If so then I'd change the query like so (and get rid of the @match variable):

    ... WHERE CustomerID = Cast(@CustomerID as INT) --if numeric
    ... WHERE CustomerNAME = @CustomerID + '%'      --if not numeric

However, if it's some weird VARCHAR field that starts with a number and ends with other data, like '11_blah', then the LIKE plus wildcard works fine

Answer 5

I'd keep it simple and assume that customer names are never numeric:

SELECT * 
FROM dbo.Customers
WHERE CustomerID LIKE @Customer + '%'
OR CustomerName LIKE @Customer + '%'

Alternatively, if you really don't want to match a numeric customer against its name, you can check like:

WHERE (IsNumeric(@Customer) = 1 AND CustomerID LIKE @Customer + '%')
OR (IsNumeric(@Customer) = 0 AND CustomerName LIKE @Customer + '%')

But then, how would you search for a customer with a numeric name? And by the way... a search like this will find customer 121 if you search for 12.

Answer 6

I believe this will do it:

WHERE CustomerID LIKE
  CASE WHEN IsNumeric(@Customer) = 1 THEN 
    CustomerID 
  ELSE
    CustomerName + '%'
  END

Answer 7

Doing a single SQL statement that tries to solve both conditions will result in worst execution plan. Remeber that SQL has to generate one single plan to satisfy any value of the @variable. In your case when @customerID is numeric the proper plan would be to use an index on CustomerID. But when @customerID is a name the proper access would be an index on CustomerName. Given this dillema the optimizer will likely pick a plan that does a full scan, ie. not optimized in neither case.

The proper thing to do is to determine in your application if is an ID or a name and call two separate stored procedures, uspGetCustomerByID and uspGetCustomerByName, according to the value entered. If you must do this via one 'magic' API entry point (the all-powerful uspGetCustomer), then you already got seveal good answers.

Answer 8

Oh just do it this way....

IF ISNUMERIC(@Customer) THEN
SELECT * FROM .... CustomerID = @Customer

ELSE 
SELECT * FROM ... CustomerName LIKE @Customer 

But you would want it more maintainable I suppose...

declare @basequery NVARCHAR(4000)
declare @params NVARCHAR(4000)

set @base_query = 'select * from dbo.Customers where '

IF ISNUMERIC(@Customer) THEN
  SET @base_query = @base_query + 'customerid = @xCustomer'
  SET @params = '@xCustomer int'
END
ELSE
  SET @base_query = @base_query + 'customerName LIKE @xCustomer + ''%'' '
  SET @params = '@xCustomer nvarchar(1000)'
END

exec sp_execuresql @base_query, @params, @Customer

Of course I would only recommend this kind of dynamic sql for more complex kinds of filtering.

Answer 9

Do something like this:

CREATE PROCEDURE [dbo].[uspGetCustomer] (
    @CustomerID INT = NULL
    @Customer VARCHAR(100) = NULL
)

AS

SET NOCOUNT ON

IF @CustomerID is not null
BEGIN
    SELECT * FROM dbo.Customers
    WHERE CustomerID = @CustomerID
END
ELSE
BEGIN
    SELECT * FROM dbo.Customers
    WHERE CustomerName LIKE @CustomerID + '%'
END

SET NOCOUNT OFF

Answer 10

Use:

CREATE PROCEDURE [dbo].[uspGetCustomer] (@Customer VARCHAR(100))
AS

BEGIN

IF ISNUMERIC(@Customer) = 1
  BEGIN
    SELECT *
      FROM dbo.CUSTOMERS
     WHERE customerid LIKE @Customer + '%'
  END
ELSE
  BEGIN
    SELECT *
      FROM dbo.CUSTOMERS
     WHERE customername LIKE @Customer + '%'
  END

END