HOW TO CODE FOR THE THIS STOREDPROCEDURE

parthiban karnan

13y1.6k.NET

I GOT A STORED PROCEDURE FOR RETRIEVING DATA FROM FILTERED SEARCH.. A DROPDOWN WITH FIRSTNAME,LASTNAME AND EMAIL AND A TEXTBOX WHICH FILTERS WITH EXACT SEARCH..

BELOW IS SP.. HOW TO CODE FOR THIS...

IF I CHOOSE FIRTSNAME FROM DDL AND ENTER FIRSTNAME IN TEXTBOX.. IT HAS TO FILTER AND BIND WITH EXACT DETAILS IN GRIDVIEW...SAME AS FOR LASTNAME AND EMAIL...

Create Procedure [dbo].[spSearch]

(

@firstName nvarchar(50) = null,

@lastName nvarchar(50) = null,

@email nvarchar(50) = null

)

BEGIN

If @firstName is not null and Len(@firstName )=0 Set @firstName = null

If @lastName is not null and Len(@lastName )=0 Set @lastName = null

If @email is not null and Len(@email )=0 Set @email = null

Select

From userinf

Where

( @firstName is null or firstName Like @firstName )

and ( @lastName is null or lastName Like @lastName )

and ( @email is null or email Like @email )

END

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sunny Sharma13y ago
Hi parthibhan,

Below is the most efficient SP you should use to filter:
--------------------------------------------------------------------------------------
CREATE PROC [dbo].[SearchStudent](@ColType varchar(10), @data varchar(100)) as
BEGIN
IF LEN(@data)>0
BEGIN
IF @ColType='fname'
SELECT * FROM students WHERE fname like ('%'+@data) or fname like ('%'+@data+'%') or fname like(@data+'%') or fname like @data ;
ELSE IF @ColType='lname'
SELECT * FROM students WHERE lname like ('%'+@data) or lname like ('%'+@data+'%') or lname like(@data+'%') or lname like @data ;
ELSE
SELECT * FROM students WHERE email like ('%'+@data) or email like ('%'+@data+'%') or email like(@data+'%') or email like @data ;

END
END

GO

(I've named this SP SearchStudent, use whatever you want it to be)
-----------------------------------------------------------------------------------
Here I assume you have three types of input: first name, last name and email. You can just pass the input type and data you want to search for that type that I've demonstrated in above SP.

EXEC SearchStudent 'fname','ra'
the statement above will list all the entries in firstname containing text 'ra'.

Happy Coding :)

Please don't forget to accept this as answer if it helps!
Thanks
parthiban karnan13y ago
NO
Sunny Sharma13y ago
have you tried implementing the code to search in TextBox_TextChanged event?
parthiban karnan13y ago
CODE NOT WORKING : WHEN I CHOOSE A ITEM FROM DROPDOWN AND ENTER SEARCH KEYWORD IN TEXTBOX.. DATA NOT DISPLAYING

public void BindData()
{

//con.Open();

SqlCommand cmd;
cmd = new SqlCommand("spsearch", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
// cmd.Parameters.Add(new SqlParameter("@roll", SqlDbType.VarChar)).Value = DropDownList1.SelectedValue;
cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar)).Value = DropDownList1.SelectedValue;
cmd.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar)).Value = DropDownList1.SelectedValue;
cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar)).Value = DropDownList1.SelectedValue;

con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
// DataSet ds = new DataSet();
DataTable dt = new DataTable("USERINF");

da.Fill(dt);
con.Close();
//con.Close();
// if (ds.Tables[1].Rows.Count > 0)
//{

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

}
Sunny Sharma13y ago
so, where are you stuck?? I mean what's the problem?