Searching an int column on the basis of a string value

Question

I have a View View_Booking in sql server 2014:

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     1
4       Lunch     1
6       Dinner    0
7       Lunch     1

While in c# I have used a gridview and casted bkStatus into string like:

<asp:Label ID="lblStatus" Text='<%# (Eval("bkStatus")+"" == "1") ? "Booked" : "Pending" %>'
    ... ></asp:Label>

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     Booked
4       Lunch     Booked
6       Dinner    Pending
7       Lunch     Booked

---

Now I'm searching into View using this query:

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus LIKE << ? >>

But don't know how to search for bkStatus which is passed as string from c# while it's a int in sql?

Kukri

Answer 1

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"

Chadbourne

Answer 2

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

Briant

Answer 3

If keyword will be status name and not status id, I would create BookingStatus table, have bkStatus and bkStatusTitle columns there and join it to the View_Booking. You could easily do LIKE on bkStatusTitle then.

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(16)) LIKE '%' + @keyword + '%' 
OR bkSlot LIKE '%' + @keyword + '%' 
OR bkStatusTitle LIKE '%' + @keyword + '%'

If keyword will be a string representation of bkStatus, I would just see if the values are same.

As a side note, it's a bad idea to build your SQL queries concatenating user input into it like '%' + keyword + '%'. This is open to SQL injection attacks. It's best to use SQL parameters to pass user input to SQL queries. Using '%' + @keyword + '%' in the SQL bit and in C# something like example below would be much safer.

sqlCommand.Parameters.Add("@keyword", SqlDbType.VarChar, 1000);
sqlCommand.Parameters["@keyword"].Value = searchText;

Parameterized queries also give you a benefit of same query text for multiple requests, which in turn allows SQL Server to cache SQL execution plans and reuse them, giving slightly better performance.

Varnish

Answer 4

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  

Nucleate

Answer 5

Just another option using CHOOSE() to decode the bkStatus and TRY_CONVERT() to test bkID.

Example

Declare @KeyWord varchar(50) = 'Pending';

Select * 
 From  View_Booking 
 Where bkID = try_convert(int,@KeyWord)
    or bkSlot like '%'+@KeyWord+'%'
    or choose(bkStatus+1,'Pending','Booked')=@KeyWord

Returns

bkID    bkSlot  bkStatus
6       Dinner  0

Mayo

@Asif.Ali This is SQL Server. The Declare (at)YourTable is a demonstrative table variable (remove), and the Declare (at)KeyWord is just a variable – Mayo

showing this error : Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the nvarchar value 'Pending' to data type int. Warning: Null value is eliminated by an aggregate or other SET operation. – Kukri

@Asif.Ali Did you use TRY_CONVERT ? (first condition of the WHERE) – Mayo

Yes I pasted your example query as it is. – Kukri

@Asif.Ali You're on 2014. What is your compatibility_level run select name, compatibility_level from sys.databases – Mayo

Here is a link to a dbfiddle dbfiddle.uk/… – Mayo

In the fiddle its working but why not at my machine? – Kukri

@Asif.Ali Are you sure 2014? Try Select @@Version – Mayo

returns Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) – Kukri

@Asif.Ali Great. What does select name, compatibility_level from sys.databases show? – Mayo

shows master 120 tempdb 120 model 120 msdb 120 ReportServer 120 ReportServerTempDB 120 Banquet_DB 120 virtualAssistanceForumDb 120 sem_db 120 – Kukri

Answer 6

Your bkStatus is integer. In the view you translate the integer value into a user meaningful string. Up to that point it's all ok. Now, for the user to search all you need to do is reverse your translations from strings to integer and search for integers.

Keeping things simple

searchStatusKey = yourVariableHodingTheString == "Booked" ? 1 : 0;

However, to avoid tedious bugs and painless upgrades of code in multiple places (say because they decided to add another status there) I would recommend a translation table here. Something like HashMap (associative array).

var statusMap = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked" },
    /* can always add more pairs in the future as needed */
};

Now, assuming your parameter is in a variable called searchStatus

searchStatusKey = statusMap.FirstOrDefault(x => x.Value == searchStatus).Key;

Now Provide as bkStatus parameter in the where part the searchStatusKey value and you're done.

select * from View_Booking where bkStatus = << ? >>

Portiere

Answer 7

I will just focus on this part of your question (Is it the question itself?):

But don't know how to search for bkStatus which is passed as string from c# while it's a int in sql?

One way of dealing with that in SQL is with the help of the CASE clause. In your specific case you could (doesn`t mean should) do something like:

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus = CASE '%"+keyword+"%' WHEN 'Booked' THEN CAST(1 AS INT) WHEN 'Pending' THEN CAST(0 AS INT) WHEN ... THEN ... ELSE ... END' 

But I suggest the use of parameters as indicated in @un-lucky's answer. There's a whole lot more we could discuss in terms of best practices here, so I suggest you to take a look at the following articles:

1. Lookup tables: You stated that bkStatus is of type INT soy I assume you could have more options than Booked or Pending, for example: Reserved or Cancelled. In that case your actual code may become increasingly untidy with every option you add.
2. Best practices for using ADO.NET: You did not specify how do you access the database from your front end. Even though this article has been around for years, most of its content is still current. I assume this may be helpful.
3. Building Better Entity Framework: In case you are using Entity Framework to access your database.

Hope it helps.

Tallula

Answer 8

Make an enum for BookingStatus and make a function that accepts string and returns the enum value. See the below code.

public enum BookingStatus {
    [Description("Pending")]
    Pending = 0,
    [Description("Booked")]
    Booked = 1
}

Now the function is as below,

    public static T GetValueFromDescription<T>(string p_description)
    {
        var type = typeof(T);
        if (!type.IsEnum) throw new InvalidOperationException();
        foreach (var field in type.GetFields())
        {
            var attribute = Attribute.GetCustomAttribute(field,
                typeof(DescriptionAttribute)) as DescriptionAttribute;
            if (attribute != null)
            {
                if (attribute.Description == p_description)
                    return (T)field.GetValue(null);
            }
            else
            {
                if (field.Name == p_description)
                    return (T)field.GetValue(null);
            }
        }
        throw new ArgumentException("Not found.", "description");
        // or return default(T);
    }

Now in the parameter in sql query, call this function with parameter as "Booked" or "Pending" and it will return enum BookingStatus.Booked. You can easily extract int value from that.

(int)BookingStatus.Booked // will give 1

Chenab

Answer 9

It looks as if you are trying to search freely amongst several columns. This is quite a common problem, and the real solution can be found at www.Sommarskog.se on dynamic search conditions.

Your solution looks as if it is vulnerable to SQL Injection. May I suggest that you implement something similar to the stored procedure search_orders_3?

Avion

it will be better to include important things in the answer. – Kukri

@AsifAli72090; well, I think my pointer to Sommarskog is important, if you care about the speed of your query. It may not be a problem on your 1GB test data, but as soon as your database is bigger than your RAM, you may have to wait for the SQL to execute. Sommarskog shows how to avoid this minefield. – Avion

@AsifAli72090; Please check if your code avoids SQL Injection. It is difficult. This is still on the top of OWASP list; owasp.org/index.php/Category:OWASP_Top_Ten_Project – Avion