Need to build a search stored procedure

I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.

I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?

This question has been answered several times in this forum. Please search for "dynamic where"/similar keywords.|||

What is the standard way of creating a search page of this sort?

|||

This thread discusses the SQL LIKE clause and dynamic queries... should be helpful:

http://forums.asp.net/thread/1529167.aspx

|||I searched dynamic where as well as many other keywords and have had no luck in finding any results. Can you offer any additional assistance?|||

SELECT *

FROM Table

WHERE (Column1 = @.col1 OR @.col1 IS NULL)

AND (Column2 = @.col2 OR @.col2 IS NULL)

AND (Column3 = @.col3 OR @.col3 IS NULL)

...

Need SQL query

Hi there. U have table structure shown here:
category (Int - AutoIncrement) parent_category(Int) Title(string)
1 0
HOME PAGE
2 1
FIRST DEPTH 1
3 1
FIRST DEPTH 2
4 1
FIRST DEPTH 3
5 2
SECOND DEPTH 1
6 3
SECOND DEPTH 2
7 3
SECOND DEPTH 3
8 6
THIRD DEPTH 1
I want 1 (one) SQL query that can return me full depth for certain caregory.
In some meta-language
for category 8 it look like this:
SELECT * FROM table WHERE category = 8
WHILE parent_category <> 1
SELECT * FROM table WHERE category = [parent_category from previous
iteration]
WEND
So in that case resulting recordset would be
category (Int - AutoIncrement) parent_category(Int) Title(string)
8 6
HOME PAGE
6 3
FIRST DEPTH 1
3 1
FIRST DEPTH 2
I know that I can do this using more that one recordset, but I want all this
in one recordset.
I am using ADO, not ADO.NET
ThanksBola,
You can achieve this using user defined function, see following example.
create table tree
(category Int, parent_category Int, Title varchar(50))
go
insert into tree
select 1, 0,'HOME PAGE' union all
select 2, 1,'FIRST DEPTH 1' union all
select 3, 1,'FIRST DEPTH 2' union all
select 4, 1,'FIRST DEPTH 3' union all
select 5, 2,'SECOND DEPTH 1' union all
select 6, 3,'SECOND DEPTH 2' union all
select 7, 3,'SECOND DEPTH 3' union all
select 8, 6,'THIRD DEPTH 1'
select * from tree
go
create function fn_get_tree (@.y int)
returns
@.tb table(empid int,
supervisor int,
empname varchar(300)
)
as
begin
declare @.x table (empid int)
insert into @.x
select parent_category from tree where category = @.y
union all
select @.y
while 1=1
begin
insert into @.x
select parent_category from tree where category in (select distinct empid from @.x)
and parent_category not in(Select empid from @.x)
if @.@.rowcount = 0
break
end
insert into @.tb
select * from tree where
exists
(select * from @.x a where a.empid= tree.category)
return
end
go
--usage
select * from fn_get_tree (7)
--
- Vishal