vijayakrishna

Create the comma separated string of role from the client side and use dynamic sql to achieve this.

Let us assume your roles string contains the value 'VP-Operations,Fresher,Administrator'

DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT Companyname, role, Location, date FROM callcenter where Location Like '''%''' + @city + '''%'''  and DATEDIFF(Day, postdate, getdate( ) ) <= @datediff and Role in ('  + @role + ')'

EXEC SP_EXECUTESQL @SQL

Actually I am using this query to bind datalist dynamically. In such case I dont know from where to call

 EXEC SP_EXECUTESQL @SQL

Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("dbo.bporesults")
        cmd.CommandType = Data.CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@datediff", beforedays)
        cmd.Parameters.AddWithValue("@city", city1)
        cmd.Parameters.AddWithValue("@role", moreabout)

        cmd.Connection = objConn


        Dim ds As New System.Data.DataSet
        Dim da As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)

        da.Fill(ds, "callcenter")
        DataList1.DataSource = ds
        DataList1.DataBind()