views:

164

answers:

10

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?

A: 

I believe this will do it:

WHERE CustomerID LIKE
  CASE WHEN IsNumeric(@Customer) = 1 THEN 
    CustomerID 
  ELSE
    CustomerName + '%'
  END
womp
Doesn't take CustomerName into account.
Randolph Potter
I didn't think it would compile, but it does work on SQL Server 2005
OMG Ponies
your are comparing CustomerID field, but in second case CustomerName should be compared
Bogdan_Ch
Whups, thanks Bogdan.
womp
+4  A: 

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

dan
I would argue that this is much harder to read, and you're basically typing the query twice.
womp
@womp: It's better than using ORs - performance and sargability. It's not ideal for multiple, optional criteria - you'd have to use dynamic SQL in that case.
OMG Ponies
+2  A: 

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
OMG Ponies
+2  A: 

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
Randolph Potter
+1  A: 

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.

Andomar
@OMG Ponies: Thanks edited
Andomar
I would have agreed with this until I watched the Bengal's game yesterday with Chad Ochocinco, yes he legally changed his last name to 85. :)
JBrooks
+3  A: 

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.

Remus Rusanu
Alternately, using three stored procedures (one for IDs, one for names, and a third that decides which to call) would also resolve the execution plan issue.
technophile
Not that I'm recommending that. ;)
technophile
Technically correct, but for less than 100.000 rows, I'd prioritize readability/maintainability over the best query plan
Andomar
@Andomar: I'm not entirely sold on that. For one the cost of scanning 100k rows over and over adds up for given a query that is run often enough. Second is that table scans increase contention, sice they need to do the S-lock on *every* row, so they're very likely to conflict with updates. I have found that more often than not the root cause of slow systems is table scans and the lock contention they create. snapshot isolation can alleviate some, but at the cost of more IO in tempdb. Performance often derives from Proper access (proper query, proper index *and* avoiding ORs in queries).
Remus Rusanu
A: 

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.

Min
+3  A: 

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
iandisme
Wow, snap! I'm a fan of the two-variable approach (as you can see). Your ISNUMERIC needs an `= 1` to be completely correct.
Randolph Potter
Ah yes thanks, changed it to = 1.
iandisme
A: 

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 + '%'
JBrooks
+1  A: 
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

Quassnoi