WHERE LIKE ID or NAME stored procedure

Question

Group, I'm trying to create a stored procedure using a single @Customer variable. What I want to do is put something in my WHERE clause that says if it is searching by number in the CustomerID field, where the number entered is LIKE CustomerID ... If char is entered, find the CustomerName field, where the text entered LIKE Customer Name. Below is an example of what I'm 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

I would do this using the IF statement, since setting this logic to WHERE makes it difficult to read later:

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  

: , CustomerID INT. , ( @match):

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

, - VARCHAR, , "11_blah", LIKE plus

Answer 2

SQL, , . , SQL @variable. , @customerID , CustomerID. @customerID - , CustomerName. , , , , , .. .

, , , uspGetCustomerByID uspGetCustomerByName, . "" API ( uspGetCustomer), .

Answer 3

, . , ( int, CustomerID varchar, CustomerName). :

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

, :

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 4

:

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

Answer 5

- :

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 6

, :

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

, , , :

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

, ? ... , , 121, 12.

Answer 7

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 + '%'

CustomerID CustomerName

Answer 8

, :

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

Answer 9

, ...

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

ELSE 
SELECT * FROM ... CustomerName LIKE @Customer 

, , ...

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

, sql .

Answer 10

, , , , .

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 + '%'