Procedure to search a table

Hi All,

I have the following table:

Code Block

Request
requestid
customername
age
sex
address
status

[status =
0 deleteme
1 addme
2 updatme

3 processed]

I need to write a stored proc that will do the following :

Input : requestid

Logic : check the customers who already have a status of 'deleteme' and have now filed a status of 'addme'. For this, inside the stored proc, i will retreive the customername and address using the requestid and then check in the table for all the customers who have the samename, address and status=0. How can i detect this.

Output : 0 for no entry found and requestid if a request of 'deleteme' for the same person was found.

For eg:

Code Block

requestid,customername,age,sex,address,status
22352,Jack,23,M,'Texas',1

I need to check if a person with the name of Jack and address Texas is there in the db with the status of 0.

Plz note: My Request table can contain more than 100000 records at a time. Please sugest accordingly

Plz help. Thanks

Here it is,

Code Block

Create Table #sample (

[requestid] int ,

[customername] Varchar(100) ,

[age] Varchar(100) ,

[sex] Varchar(100) ,

[address] Varchar(100) ,

[status] int

);

Insert Into #sample Values('20352','Jack','23','M','Texas','0');

Insert Into #sample Values('22352','Jack','23','M','Texas','1');

Insert Into #sample Values('22353','Jack','23','M','Austin','0');

Insert Into #sample Values('22362','Buck','23','M','Austin','0');

Insert Into #sample Values('22392','Buck','23','M','Austin','1');

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 0

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] < [add].[requestid]

and [add].[status] = 1

)

|||

Hi,

Thanks for query. I ran the query and it returns all people who have status as 1.

I want all people who have status as 0 and not 1. How can i make this in a stored proc so that i accept only request id and then search.

thanks.

|||

Expected one.. Use the following query..

Code Block

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 1

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] > [add].[requestid]

and [add].[status] = 0

)

|||

thanks so strange..i tried that previously on the query you gave first..it gave no records..no when i try yours, it gave the needed results..

nyways thanks.