Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Requirement:

GetEmployees will have following parameters.
 @EmployeeName - By Default NULL
 @Country - By Default NULL
 @Department - By Default NULL
 @SalaryStart - By Default NULL
 @SalaryEnd - By Default NULL

examples,

o Exec GetEmployees NULL, NULL, NULL, NULL, NULL – This will list all employees.
o Exec GetEmployees “ROCK”, NULL, NULL, NULL, NULL – This will list all employees who has word “ROCK” in EmployeeName order by department.
o Exec GetEmployees “ROCK”, NULL, NULL, 500, NULL – This will list all employees who has word “ROC” in EmployeeName

What I have tried:

Alter proc GetEmployeedata (@EmployeeName varchar(50)=null,@Country varchar(50)=null,
@Department varchar(50)=null,@SalaryStart decimal(10,2)=null,@SalaryEnd decimal(10,2)=null)
As
Begin
select e.empId,e.empName,e.Salary,e.Dateofbirth,c.country,d.departmentName from Employee e
join Department d
on e.departmentId=d.departmentId
join Country c
on e.countryId=c.countryId
where (e.empName Like '%'+@EmployeeName+'%' or ISNULL(@EmployeeName,'')='') and
(c.country Like '%'+@Country+'%' or ISNULL(@Country,'')='') and
(d.departmentName like '%'+@Department+'%' or ISNULL(@Department,'')='') and
(e.Salary>@SalaryStart or ISNULL(@SalaryStart,'')='') and
(e.Salary<@SalaryEnd or ISNULL(@SalaryStart,'')=''
end
Posted
Updated 5-Feb-16 7:13am
v2
Comments
Richard Deeming 5-Feb-16 13:17pm    
You've told us what the requirements are; you've shown us what you've tried so far; but you've forgotten to tell us what the problem is!

Are you getting an error? If so, tell us what the error message is.

Are you not getting the expected results? If so, show us the parameters, the raw data, the expected results, and the actual results.

If possible, create a SQLFiddle[^] with some representative data to reproduce the problem.
jgakenhe 5-Feb-16 13:18pm    
Use Dynamic SQL. Please see the article: http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

About half way down is what you need.
ZurdoDev 5-Feb-16 13:34pm    
I disagree, dynamic SQL should be avoided unless necessary. Here, I don't see why it would be.
jgakenhe 5-Feb-16 13:39pm    
The side effects are an ugly query that is difficult to read and is un-maintainable.
ZurdoDev 5-Feb-16 13:40pm    
Really? I think dynamic sql is much more difficult to read and maintain.

To each their own I suppose.

1 solution

This is possible & does not require dynamic SQL.
Re-order your query parameters as per below

SQL
select e.empId,e.empName,e.Salary,e.Dateofbirth,c.country,d.departmentName from Employee e
join Department d
on e.departmentId=d.departmentId
join Country c
on e.countryId=c.countryId
where 
(@EmployeeName IS NULL OR (e.empName Like '%'+@EmployeeName+'%'))
and 
(@Country IS NULL OR (c.country Like '%'+@Country+'%') 
and 
(@Department IS NULL OR (d.departmentName like '%'+@Department+'%') 
and 
(@SalaryStart IS NULL OR (e.Salary>@SalaryStart)) 
and 
(@SalaryEnd IS NULL OR (e.Salary<@SalaryEnd))


With specific datatypes you may find this returns unexpected results. In these cases I typically use an alternate value, such as -1 for INT, empty string for varchar etc.

Kind Regards
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900