CODEWITHSUNDEEP
c#sql-serverwinformssearchstored-procedures
Mohammed Shfq
Mohammed Shfq

Reputation: 65

multiple field search using stored procedure

I have 24 fields in my table all of them are of nvarchar type except one of int type which is ID now I want to search in all fields for any text written by the user in textBox for example if the user will write "20" in the textBox it then should show all records that their fields have "20" .. I've written a stored procedure but it is not working whenever I write any thing that is existing in the fields of my table it doesn't return any thing in the data grid view !!
This is my stored procedure: 

create proc searchClientProfile
@search nvarchar(255)
as
select * from clientProfile where convert(nvarchar,ID)+compName+Addres+compPhone+compEmail+compWebsite+shipAddress+shipPhone+primeContact+primePhone+primeEmail+secContact+secPhone+secEmail+sector+established+industry+anulPrch+country+region+town+stat+city+zip like '%@search%'

And this is the code in my textBox TextChanged event: 

cmd = new SqlCommand("searchClientProfile", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parm = new SqlParameter[1];
parm[0] = new SqlParameter("@search", SqlDbType.NVarChar, 255);
parm[0].Value = searchClientTxt.Text.Trim();
cmd.Parameters.AddRange(parm);
da = new SqlDataAdapter(cmd);
da.Fill(getSearchedClientProfiles);
searchClientsDgv.DataSource = getSearchedClientProfiles;

Upvotes: 1

Views: 1809

Answers (4)

Mohammed Shfq
Mohammed Shfq

Reputation: 65

Thanks a lot for help guys ... Form the answers seen I altered my stored procedure specially the like part from '%@search%' to '%'+@search+'%' second in my C# code I've added to TextChanged event a line of code that clears the previous content of data grid view so now the code of TextChanged event looks like: 

getSearchedClientProfiles.Clear();
cmd = new SqlCommand("searchClientProfile", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] parm = new SqlParameter[1];
parm[0] = new SqlParameter("@search", SqlDbType.NVarChar, 255);
parm[0].Value = searchClientTxt.Text.Trim();
cmd.Parameters.AddRange(parm);
da = new SqlDataAdapter(cmd);
da.Fill(getSearchedClientProfiles);
searchClientsDgv.DataSource = getSearchedClientProfiles;

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

Another option (clearly not sargable).

Note: an ALIAS is required.

Example

Select * 
 From clientProfil A
 Where (Select A.* for XML Raw) like '%'+@search+'%'

Upvotes: 2

sapi
sapi

Reputation: 244

If I understand properly you want to check if a column of of your 20 contains your search query. It should look something like that :

select * 
from clientProfile 
where convert(nvarchar,ID) like '%@search%'
OR compName like '% ' + @search + '%'
OR Addres like '%'+@search + '%'
OR compPhone like '%'+@search + '%'
OR compEmail like '%'+@search + '%'
OR compWebsite like '%'+@search + '%'
OR shipAddress like '%'+@search + '%'
OR shipPhone like '%'+@search + '%'
OR primeContact like '%'+@search + '%'
OR primePhone like '%'+@search + '%'
OR primeEmail like '%'+@search + '%'
OR secContact like '%'+@search + '%'
OR secPhone like '%'+@search + '%'
OR secEmail like '%'+@search + '%'
OR sector like '%'+@search + '%'
OR established like '%'+@search + '%'
OR industry like '%'+@search + '%'
OR anulPrch like '%'+@search + '%'
OR country like '%'+@search + '%'
OR region like '%'+@search + '%'
OR town like '%'+@search + '%'
OR stat like '%'+@search + '%'
OR city like '%'+@search + '%'
OR zip like '%'+@search + '%'

I don't know how you built your application but the performances will not be amazing on that kind a query

Upvotes: 1

gotqn
gotqn

Reputation: 43646

In concatenation, you should handle NULL values. It can be done using ISNULL or CONCAT.

Also, what if in ecContact and secPhone you have sea and rch correspondingly? You will concatenate them as search and have a match. You need to use a separator.

If I were you, I will just write the condition check for column.

create proc searchClientProfile
' + @search + ' nvarchar(255)
as
select * from clientProfile where convert(nvarchar,ID) like '%' + @search + '%' OR compName like '%' + @search + '%' OR Addres like '%' + @search + '%' OR compPhone like '%' + @search + '%' OR compEmail like '%' + @search + '%' OR compWebsite like '%' + @search + '%' OR shipAddress like '%' + @search + '%' OR shipPhone like '%' + @search + '%' OR primeContact like '%' + @search + '%' OR primePhone like '%' + @search + '%' OR primeEmail like '%' + @search + '%' OR secContact like '%' + @search + '%' OR secPhone like '%' + @search + '%' OR secEmail like '%' + @search + '%' OR sector like '%' + @search + '%' OR established like '%' + @search + '%' OR industry like '%' + @search + '%' OR anulPrch like '%' + @search + '%' OR country like '%' + @search + '%' OR region like '%' + @search + '%' OR town like '%' + @search + '%' OR stat like '%' + @search + '%' OR city like '%' + @search + '%' OR zip like '%' + @search + '%'

Upvotes: 0

Related Questions