Hi
You have to think through your problem. Do you want people to search on either name or address and return all related data? Or do you want to search on name and address and they have to line up or no result will be returned. Logical operators are a good to understand when working with datasets. See
Logical Operators (Transact-SQL)[
^]
Here is an example that you can use for your code:
CREATE TABLE #Dept
(
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50) NULL,
[Address] VARCHAR(120) NULL,
scholaid VARCHAR(50) NULL
)
INSERT INTO #Dept
(name ,[Address], scholaid )
VALUES('Ben Smith','1 Smith Street','No'),
('Sam Coldwell','1 Sam Street','No'),
('John Max','1 Max Street','Yes'),
('Maggie Curtis','1 Curtis Street','No'),
('Susan Jason','1 Jason Street','Yes')
INSERT INTO #Dept
([Address], scholaid )
VALUES('2 Mystreet','No')
INSERT INTO #Dept
(name , scholaid )
VALUES('Chris Mason','No')
DECLARE @name VARCHAR(50) = NULL,
@Address VARCHAR(500) = NULL
SET @name = 'Smith'
SET @Address = 'Smith'
SELECT u.id,
u.name,
u.Address,
u.scholaid
FROM #Dept u
WHERE (ISNULL(u.name,'') like '%'+ISNULL(@name,'')+'%') AND (ISNULL(u.Address,'') like '%'+ISNULL(@Address,'')+'%')
SET @name = 'Smith'
SET @Address = 'Smith'
SELECT u.id,
u.name,
u.Address,
u.scholaid
FROM #Dept u
WHERE (ISNULL(u.name,'') like '%'+ISNULL(@name,'')+'%')
UNION
SELECT u.id,
u.name,
u.Address,
u.scholaid
FROM #Dept u
WHERE (ISNULL(u.Address,'') like '%'+ISNULL(@Address,'')+'%')
SET @name = 'Smith'
SET @Address = 'Max'
SELECT u.id,
u.name,
u.Address,
u.scholaid
FROM #Dept u
WHERE (ISNULL(u.name,'') like '%'+ISNULL(@name,'')+'%')
UNION
SELECT u.id,
u.name,
u.Address,
u.scholaid
FROM #Dept u
WHERE (ISNULL(u.Address,'') like '%'+ISNULL(@Address,'')+'%')
DROP TABLE #Dept