Conversion failed when converting the varchar value '%' to data type int in SQL Server sto-CodePudding

I am working on a SQL Server stored procedure. I have table with these columns

BusName(NVarchar(100) not null)
BusNumber(int, null)

in my main Bus table.

I am trying to implement search functionality where user can either pass BusName or BusNumber to get all the results stored in main Bus table

I have implemented this stored procedure to handle the search functionality:

CREATE PROCEDURE [dbo].[spGetAllBus]
    @BusName VARCHAR(512),
    @BusNo INT
AS
    SET NOCOUNT ON;  
BEGIN
     SELECT 
         [BusName], 
         [ManagingOwner],
         [Operator],
         [CurrentService], [CurrentRate],
         [BusNumber],
         [MarketRate],
         [EarliestRdel]
     FROM 
         [dbo].[Bus] 
     WHERE 
         [BusName] LIKE '%'   @BusName   '%' 
         OR [BusNumber] LIKE '%'   @BusNo   '%' 
END

When I execute this stored procedure like this:

DECLARE @return_value int

EXEC    @return_value = [dbo].[spGetAllBus]
        @BusName = N'emma',
        @BusNo = NULL
SELECT  'Return Value' = @return_value

I get an error

Conversion failed when converting the varchar value '%' to data type int.

I am struggling to find the root cause as I am not having any conversions in the code. Can someone help me here. Any help would be appreciated. Thanks

CodePudding user response：

You can alter your store procedure like below if your BusNumber column's type is INT.

ALTER PROCEDURE [dbo].[spGetAllBus]
   @BusName VARCHAR(512),
   @BusNo INT
AS
   SET NOCOUNT ON;  
BEGIN
 SELECT 
     [BusName], 
     [ManagingOwner],
     [Operator],
     [CurrentService], [CurrentRate],
     [BusNumber],
     [MarketRate],
     [EarliestRdel]
 FROM 
     [dbo].[Bus] 
 WHERE 
     [BusName] LIKE '%'   @BusName   '%' 
     OR [BusNumber] = @BusNo 
END