Text box where user enters name,can be first name or last name or both then record data is displayed on to screen.Stored procedure logic fails

See more:

SQL-Server

stored-procedure

have a text box where user enters name can be first name,last name or both.This all is entered in a single user input text box.Then the sql stored procedure should search for name(first,name,last name) can even be multiple records where the user can select a specific name from repetitive name in a popup or a simple table. I am don't have much skills in sql server.I am having problems getting the logic right.

The code till now is as follows-

create PROCEDURE [dbo].[getfullnamereturnTest]
@NameSearch nvarchar(max)
AS

begin

   if exists (select * from testingtable where firstname='@NameSearch') 
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname ='@NameSearch'
group by testingtable.firstname
having count(testingtable.firstname)>1)
subq ON testingtable.firstname = subq.firstname
end
else if exists (select * from testingtable where lastname='@NameSearch')
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.lastname ='@NameSearch'
group by testingtable.lastname
having count (testingtable.lastname)>1 )
subq ON testingtable.lastname = subq.lastname
end

else
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname+testingtable.lastname ='@NameSearch'
group by testingtable.firstname,testingtable.lastname
having count (testingtable.firstname+testingtable.lastname)>1 ) 
subq ON testingtable.firstname = subq.firstname AND testingtable.lastname = subq.lastname
end
end
go

Searching for last name with 'reddy'

exec getfullnamereturnTest @NameSearch='reddy'

The stored procedure returns empty table.Even when it consists of multiple records on name.But when i just executed the query without input parameter as below and it works fine.

 if exists (select * from testingtable where firstname='reddy') 
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname ='reddy'
group by testingtable.firstname
having count(testingtable.firstname)>1)
subq ON testingtable.firstname = subq.firstname
end
else if exists (select * from testingtable where lastname='reddy')
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.lastname ='reddy'
group by testingtable.lastname
having count (testingtable.lastname)>1 )
subq ON testingtable.lastname = subq.lastname
end

else
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname+testingtable.lastname ='reddy'
group by testingtable.firstname,testingtable.lastname
having count (testingtable.firstname+testingtable.lastname)>1 ) 
subq ON testingtable.firstname = subq.firstname AND testingtable.lastname = subq.lastname
end

It returns output as below:

firstname	  lastname	country
    amulya	  Chdary	Uk
    amulya	  reddy	        BZ

The table consists of the following values.

firstname	lastname	country
sruthi       	 reddy	         IND
sruthi	        chlka	        US
amulya	        Chdary	        Uk
sruthi	        K	        AUS
amulya	        reddy	        BZ
avasya          Koneru	        US
sruthi	        reddy	      AUS
amulya	        reddy	        BZ
avasya	        Koneru	        US
avasya	        koneru	        AUS

Table data

CREATE TABLE [dbo].[testingtable](  [firstname] [nvarchar](50) NULL,    [lastname] [nvarchar](50) NULL,     [country] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO testingtable (firstname, lastname, country)
   VALUES ('sruthi','reddy' , 'IND'),
          ('sruthi','chlka' , 'US'),
          ('amulya', 'reddy', 'BZ'),
          ('amulya', 'Chdary', 'Uk'),
          ('avasya', 'Koneru', 'US'),
           ('avasya', 'Koneru', 'AUS'),
          ('amulya', 'reddy', 'BZ'),
           ('avasya', 'Koneru', 'US'),
           ('sruthi','K' , 'AUS'),
           ('sruthi','reddy' , 'AUS');