Searching an int column on the basis of a string value

So you need a search box in which user can search by using bkID,bkSlot or bkStatus, If the search text is Booked or Pending we have to add the filter for bkStatus which will be an integer field in the database. right? Few more thing that I have to mention here is the usage of using as well as the parameterization for queries for a smarter and safer way of execution. So I would like to suggest to build and execute the query like the following:

int statusCode = -1;
if(keyword.ToLower() == "booked")
   statusCode = 1;
else if(keyword.ToLower() == "pending")
   statusCode = 0;
string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection("connectionString here"))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.int).value = statusCode;
        sqlCommand.ExecuteNonQuery();
     }
}

Please note the following:

• If you want to include the bkStatus filter for book, Pend etc.. then you have to change the condition accordingly by using .Contains() or .StartsWith() instead for .ToLower()
• statusCode is initialized with -1 to avoid bkStatus based filter for all other values

You can use declare function to create a temporary table that has a list of bkStatus.

It will be easier for you to create a query by using bkstatus as a foreign key. After that, you don't have to use cast or like function anymore. It will be a little bit inefficient.

You can try this code below :

declare @bkstatus table (number int primary key , bkstatus varchar(10) )
insert into @bkstatus (number , bkstatus)
values ( 0 , 'Pending'), (1 , 'Booked')

and then using this query :

SELECT * FROM View_Booking v
INNER JOIN @bkstatus b on v.bkstatus = b.number
WHERE b.bkstatus =  @keyword  

Some recommendations

The query you have provided need to be optimized:

1. First, using CAST(bkID AS NVARCHAR(MAX)) will affect the performance of the query, because it will not use any index, also casting to NVARCHAR(MAX) will decrease the performance.

2. bkStatus is a numeric column so you have to use = operator and compare with numeric values (0 or 1 or ...), also the text values provided are defined in the asp tag not in the database, so they are used in the application level not the data level.

3. if you are using CAST(bkID AS NVARCHAR(MAX)) to search for the bkid column that contains a specific digit (ex: search for 1 -> result 1,10,11,...), then try Casting to a specific size (ex: CAST(bkID as NVARCHAR(10))

4. It is recommended to use parameterized queries for a better performance and to prevent Sql injection attacks. look at @un-lucky answer

5. You can use a dictionary Object to store the ID values related to the keywords

Example

Note: The use of CAST and Like will not used any index, this example is based on your requirements (i tried to combine the recommendations i provided with others recommendations)

var dicStatus = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked"  },
    { 2, "Cancelled" }
    // ...
};

string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(10)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.Int).value = dicStatus.FirstOrDefault(x => x.Value == keyword).Key;
        sqlCommand.ExecuteNonQuery();
     }
}

Also if BkID is an integer column it is better to use

sqlCommand.Parameters.Add("@bkID", SqlDbType.Int).value = (Int)keyword ;

References & Helpful Links

• Like operator for integer
• Performance hit using CAST in T-SQL
• How much do CAST statements affect performance?
• SQL Server: Index columns used in like?
• C# Dictionary get item by index
• Getting query to work with parameter and "like"