CODEWITHSUNDEEP
sql-server
Muzafar Khan
Muzafar Khan

Reputation: 827

Select query from many-to-many relationship

I have these tables in my database

enter image description here

My stored procedure is

ALTER procedure [dbo].[SearchEmployee]
        @Name varchar(155),
        @Gender varchar(50),
        @MaterialStatus varchar(50),
        @DOB date,
        @Designation varchar(155),
        @Salary money
AS
    SET NOCOUNT OFF;
    BEGIN
        SELECT
            intEmployeeId, 
            (vchFirstName + ' ' + ISNULL(vchMiddleName,' ') + ' ' +         vchLastName) AS Name, 
            vchGender , 
            vchMaterialStatus,
            dtDOB,
            vchDesignation,
            intSalary,  
            dtmCreatedDate,
            bitActive,
            F.vchFilename As FileName ,
            F.intFileid As Fileid 
        FROM
            Employee AS E
        FULL OUTER JOIN
            EmployeeFiles AS EF ON e.intEmployeeId = EF.intEmployeeIds
        FULL OUTER JOIN
            Files AS F ON EF.intFileids = F.intFileid
        WHERE
            vchFirstName LIKE @Name OR
            vchMiddleName LIKE @Name OR
            vchLastName LIKE @Name 
    END

This query returns only one row

exec SearchEmployee @Name='m', @Gender=NULL, @MaterialStatus=NULL, 
                    @DOB='0001-01-01', @Designation=NULL, @Salary=NULL

I have multiple employees with a name containing the m character. How to get all rows from the database?

Upvotes: 0

Views: 47

Answers (1)

Mahesh
Mahesh

Reputation: 8892

From what I understand that you need to get all the records where name contains M in them. So you need to put the wild card character % to tell the executioner that % infront of M means any number of any characters before M and after the M means any number of any characters after the M. Read more about it here

So for that you need to alter your like condition

    vchFirstName like '%'+@Name+'%' OR
    vchMiddleName like '%'+@Name+'%' OR
    vchLastName like '%'+@Name+'%'

Upvotes: 2

Related Questions