CODEWITHSUNDEEP
sql-serverstored-procedures
user3049119
user3049119

Reputation: 11

sql select query is not working in stored procedure

In my project I have written a SQL query with in stored procedure like below.

select 
    HostID, HostUniqueID,
    (RegFirstName+' '+ RegLastName) as RegistrantName,
    (CoRegFirstName+' '+ CoRegLastName) as CoRegistrantName, 
    EventType,    
    convert(varchar, EventDate, 101) as EventDate,
    Emailid 
from 
    tbl_HostDtls 
where 
    Del_sw = 'N' 
    and RegFirstName like'%'+ @Hostfirstname +'%' 
    and RegLastName like '%'+ @hostlastname +'%' 
    or CoRegFirstName like'%'+ @Hostfirstname  +'%' 
    and CoRegLastName like '%'+ @Hostfirstname  +'%'

I am sending two parameters into the above query: @Hostfirstname, @hostlastname.

Here in the query Del_sw='N' means the record is not deleted.

If user wants to get the data based on registry first name and last name, then user can get the not deleted data perfectly.

Else user wants to get the data based on the co_registry first name and last name the "and" condition is not working that means this query fetching the deleted records also(its not considering Del_sw='N' condition)

Note: deleted records also stored in my database.

Upvotes: 1

Views: 969

Answers (2)

Raj
Raj

Reputation: 10843

Select HostID,
  HostUniqueID,
  (RegFirstName+' '+ RegLastName) as RegistrantName,
  (CoRegFirstName+' '+CoRegLastName) as CoRegistrantName,
  EventType,
  convert(varchar,EventDate,101) as EventDate,
  Emailid
 from tbl_HostDtls
 where Del_sw='N' and
 ((RegFirstName like'%'+ @Hostfirstname +'%' 
 and RegLastName like '%'+ @hostlastname +'%') 
 or  (CoRegFirstName like'%'+ @Hostfirstname  +'%' 
 and CoRegLastName like '%'+ @Hostfirstname  +'%'))

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Use brackets correctly to encapsulate your conditions in WHERE clause

where Del_sw='N' and ( (RegFirstName like'%'+ @Hostfirstname +'%' and RegLastName like '%'+ @hostlastname +'%' ) or (CoRegFirstName like'%'+ @Hostfirstname +'%' and CoRegLastName like '%'+ @Hostfirstname +'%') )

Since you want search from not deleted records only, capture rest of condition in single pair of brackets after specifying not deleted record condition.

Upvotes: 1

Related Questions