How to Use 'Like' with a parameter

Question

I want to search for a number embedded in a string in a field in our log table using a parameter.

select * from vwLogs where log_time >'02/24/2009' and message like ('%2009022508241446%')

I know how to use parameters when the where clause is an equals sign but not sure how to do it with 'Like'

this doesn't seem right

 WHERE message like ('%@ErrorMessage%')

I just tried this and it didn't work. The only thing new is the message search part

protected void btnRunQuery_Click(object sender, EventArgs e)
    {
        string strConn, strSQL;
        strConn = @";";
        strSQL = @"SELECT * FROM weblogs.dbo.vwlogs WHERE Log_time >= @BeginDate AND Log_Time < @EndDate AND (client_user=@UserName OR @UserName IS NULL) AND (message like '%' + @ErrorNumber + '%' OR @ErrorNumber IS NULL) ORDER BY Log_time DESC";

        using (SqlConnection cn = new SqlConnection(strConn))
        {
            SqlCommand cmd = new SqlCommand(strSQL, cn);


            cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
            cmd.Parameters.AddWithValue("@ErrorNumber", txtErrorNumber.Text);

            cmd.Parameters.Add("@BeginDate", SqlDbType.DateTime).Value =
                DateTime.Parse(txtBeginDate.Text).Date;
            cmd.Parameters.Add("@EndDAte", SqlDbType.DateTime).Value =
                // add one to make search inclusive
                DateTime.Parse(txtEndDate.Text).Date.AddDays(1);


            cn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();

            GridView1.DataSource = rdr;
            GridView1.DataBind();

            cn.Close();
        }
    }

Thanks for the help

I got this to work

   if (string.IsNullOrEmpty(txtUserName.Text))
   {
       cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = DBNull.Value; 
   }
   else
   {
       cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = txtUserName.Text;
   }

   if (string.IsNullOrEmpty(txtErrorNumber.Text))
   {
       cmd.Parameters.Add("@ErrorNumber", SqlDbType.VarChar, 50).Value = DBNull.Value;
   }
   else
   {
       cmd.Parameters.Add("@ErrorNumber", SqlDbType.VarChar, 50).Value = txtErrorNumber.Text;
   }

Joel Coehoorn · Answer 1 · 2009-02-25T21:49:46.123

WHERE message like '%' + @ErrorMessage + '%'

Based on your edit I don't immediately see what's causing your error, but I did spot two potential issues:

It's not handling null ErrorNumbers correctly. I don't think this is it, because an empty string should still match everything for that query. But fixing the nulls will improve performance for that case.
It's treating it as a numeric type rather than a varchar. This also has performance implications and might actually break the LIKE query: I don't recall what the behavior is off the top of my head.

Try this:

protected void btnRunQuery_Click(object sender, EventArgs e)
{
    string strConn = @";";
    string strSQL =
         "SELECT * "
      + " FROM weblogs.dbo.vwlogs"
      + " WHERE Log_time >= @BeginDate AND Log_Time < @EndDate"
          + " AND (client_user=@UserName OR @UserName IS NULL)" 
          + " AND (message like '%' + @ErrorNumber + '%' OR @ErrorNumber IS NULL)"
      + " ORDER BY Log_time DESC";

    using (SqlConnection cn = new SqlConnection(strConn))
    using (SqlCommand cmd = new SqlCommand(strSQL, cn))
    {
        cmd.Parameters.Add("@BeginDate", SqlDbType.DateTime).Value =
            DateTime.Parse(txtBeginDate.Text).Date;
        cmd.Parameters.Add("@EndDAte", SqlDbType.DateTime).Value =
            // add one to make search inclusive
            DateTime.Parse(txtEndDate.Text).Date.AddDays(1);
        cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = 
            string.IsNullOrEmpty(txtUserName.Text) ? DBNull.Value : txtUserName.Text;
        cmd.Parameters.Add("@ErrorNumber", SqlDbType.VarChar, 50).Value =
            string.IsNullOrEmpty(txtErrorNumber.Text) ? DBNull.Value : txtErrorNumber.Text;

        cn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();

        GridView1.DataSource = rdr;
        GridView1.DataBind();
    }
}

BTW: didn't I give you that code in the first place? :)

yeah it worked great then I had the idea to be able to search by log number and nut just username and that's when i started a having problems because the error number is embedded in a string. — , Feb 25 '09 at 21:50
Now I'm getting this error message Error 1 Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'string' — , Feb 25 '09 at 21:51
D'oh! Forgot about the little quirk. That's right you have to expand that out into a longer if/else construct rather than the nice, concise ternary operator. — Joel Coehoorn, Feb 25 '09 at 22:09

score 4 · Answer 2 · answered Feb 25 '09 at 20:58

4

You are on the right path, but use it this way:

SET @ErrorMessage = '%' + @ErrorMessage + '%'


SELECT messageId FROM [yourTable]
WHERE message like @ErrorMessage

Otherwise the server will not be able to cache the execution plan

answered Feb 25 '09 at 20:58

Dimi Takis

4,924
3
29
41

score 1 · Answer 3 · answered Feb 25 '09 at 20:57

1

or if @ErrorMessage contains the % already, e.g. @ErrorMessage = 'ABCD%' then this will also work

... WHERE message like @ErrorMessage

answered Feb 25 '09 at 20:57

MikeW

5,702
1
35
43

How to Use 'Like' with a parameter

3 Answers3

Linked

Related