<o:p>
We must show all employees that belongs to a publisher or thoses who has a certain job.
The User Interface looks like that:
<o:p>
<o:p>
<o:p>
Where you can filter jobs choosing a job or (All jobs) , and too you can filter by publisher choosing one of then or (All Publishers).
<o:p>
The Stored Procedure that solves the query is something like that:
<o:p>
Making several SELECT sentences<o:p>
<o:p>
<o:p>
CREATE PROCEDURE dbo.GetEmployeesFiltered1<o:p>
<o:p>
(<o:p>
@JobFilter int ,<o:p>
@PublisherFilter int<o:p>
)<o:p>
<o:p>
AS<o:p>
<o:p>
begin<o:p>
<o:p>
declare @FilterByJob bit<o:p>
declare @FilterByPub bit<o:p>
<o:p>
if @JobFilter > 0<o:p>
begin<o:p>
select @FilterByJob = 1<o:p>
end <o:p>
else<o:p>
begin<o:p>
select @FilterByJob = 0<o:p>
end <o:p>
<o:p>
if @PublisherFilter > 0<o:p>
begin<o:p>
select @FilterByPub = 1<o:p>
end <o:p>
else<o:p>
begin<o:p>
select @FilterByPub = 0<o:p>
end <o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
if @FilterByJob=1<o:p>
BEGIN<o:p>
if @FilterByPub=1<o:p>
BEGIN<o:p>
--Filter By Job and Pub<o:p>
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>
FROM jobs INNER JOIN<o:p>
employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>
publishers ON employee.pub_id = publishers.pub_id<o:p>
<o:p>
WHERE<o:p>
publishers.pub_id = @PublisherFilter<o:p>
OR<o:p>
jobs.job_id = @JobFilter<o:p>
<o:p>
END <o:p>
<o:p>
else<o:p>
--Filter by Job<o:p>
BEGIN<o:p>
<o:p>
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>
FROM jobs INNER JOIN<o:p>
employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>
publishers ON employee.pub_id = publishers.pub_id<o:p>
<o:p>
WHERE<o:p>
jobs.job_id = @JobFilter<o:p>
END <o:p>
<o:p>
END<o:p>
<o:p>
else<o:p>
--Does not filter by job<o:p>
<o:p>
BEGIN<o:p>
if @FilterByPub=1<o:p>
BEGIN<o:p>
<o:p>
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>
FROM jobs INNER JOIN<o:p>
employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>
publishers ON employee.pub_id = publishers.pub_id<o:p>
<o:p>
WHERE<o:p>
publishers.pub_id = @PublisherFilter<o:p>
<o:p>
END <o:p>
else<o:p>
--Does not filter by Pub<o:p>
BEGIN<o:p>
<o:p>
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>
FROM jobs INNER JOIN<o:p>
employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>
publishers ON employee.pub_id = publishers.pub_id<o:p>
<o:p>
END <o:p>
<o:p>
END<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
end<o:p>
<o:p>
<o:p>
<o:p>
The problem of that kind of procedures is that heavy to maintain and you must remember to maintain the SELECT sentence for all options/filters.<o:p>
<o:p>
<o:p>
So that can be solved in that way<o:p>
<o:p>
<o:p>
Executing a built SELECT sentence via the EXEC command<o:p>
<o:p>
<o:p>
CREATE PROCEDURE dbo.GetEmployeesFiltered2<o:p>
<o:p>
(<o:p>
@JobFilter int ,<o:p>
@PublisherFilter int<o:p>
)<o:p>
<o:p>
AS<o:p>
<o:p>
begin<o:p>
<o:p>
declare @FilterByJob bit<o:p>
declare @FilterByPub bit<o:p>
<o:p>
if @JobFilter > 0<o:p>
begin<o:p>
select @FilterByJob = 1<o:p>
end <o:p>
else<o:p>
begin<o:p>
select @FilterByJob = 0<o:p>
end <o:p>
<o:p>
if @PublisherFilter > 0<o:p>
begin<o:p>
select @FilterByPub = 1<o:p>
end <o:p>
else<o:p>
begin<o:p>
select @FilterByPub = 0<o:p>
end <o:p>
<o:p>
<o:p>
<o:p>
declare @mySentece varchar (1000) <o:p>
declare @myFilter varchar (1000)<o:p>
<o:p>
<o:p>
<o:p>
select @mySentece = 'SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>
FROM jobs INNER JOIN<o:p>
employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>
publishers ON employee.pub_id = publishers.pub_id'<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
if @FilterByJob=1<o:p>
BEGIN<o:p>
if @FilterByPub=1<o:p>
BEGIN<o:p>
--Filter By Job and Pub<o:p>
SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter + '<o:p>
OR<o:p>
jobs.job_id = ' + @JobFilter<o:p>
END <o:p>
<o:p>
else<o:p>
--Filter by Job<o:p>
BEGIN <o:p>
SELECT @myFilter = 'jobs.job_id = ' + @JobFilter<o:p>
END <o:p>
<o:p>
END<o:p>
<o:p>
else<o:p>
--Does not filter by job<o:p>
<o:p>
BEGIN<o:p>
if @FilterByPub=1<o:p>
BEGIN<o:p>
<o:p>
SELECT @myFilter = 'publishers.pub_id = ' + @PublisherFilter<o:p>
<o:p>
END <o:p>
else<o:p>
--Does not filter by Pub<o:p>
BEGIN <o:p>
SELECT @myFilter = ''<o:p>
END <o:p>
END<o:p>
<o:p>
<o:p>
end<o:p>
<o:p>
<o:p>
<o:p>
if @myFilter = ''<o:p>
begin<o:p>
exec ( @mySentece )<o:p>
end <o:p>
else<o:p>
begin<o:p>
exec ( @mySentece + ' WHERE ' + @myFilter )<o:p>
end <o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
Here you solve the problem of maintaining the SELECT sentence for all options/filters, but it has the problem that SQL Server must compile the stored procedure on every execution.<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
Multi filter SELECT sentences<o:p>
<o:p>
And here is where becames the solution I propose to a Query with many filters<o:p>
<o:p>
<o:p>
<o:p>
CREATE PROCEDURE dbo.GetEmployeesFiltered3<o:p>
<o:p>
(<o:p>
@JobFilter int ,<o:p>
@PublisherFilter int<o:p>
)<o:p>
<o:p>
AS<o:p>
<o:p>
begin<o:p>
<o:p>
declare @FilterByJob bit<o:p>
declare @FilterByPub bit<o:p>
<o:p>
if @JobFilter > 0<o:p>
begin<o:p>
select @FilterByJob = 1<o:p>
end <o:p>
else<o:p>
begin<o:p>
select @FilterByJob = 0<o:p>
end <o:p>
<o:p>
if @PublisherFilter > 0<o:p>
begin<o:p>
select @FilterByPub = 1<o:p>
end <o:p>
else<o:p>
begin<o:p>
select @FilterByPub = 0<o:p>
end <o:p>
<o:p>
<o:p>
<o:p>
<o:p>
SELECT jobs.job_desc AS Expr1, publishers.pub_name AS Expr2, employee.*<o:p>
FROM jobs INNER JOIN<o:p>
employee ON jobs.job_id = employee.job_id INNER JOIN<o:p>
publishers ON employee.pub_id = publishers.pub_id<o:p>
<o:p>
WHERE <o:p>
(@FilterByPub=1 AND publishers.pub_id = @PublisherFilter )<o:p>
<o:p>
OR <o:p>
<o:p>
(@FilterByJob=1 AND jobs.job_id = @JobFilter )<o:p>
End<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
The main step is to establish which filters to “activate”. When this indicator is turned on , it will apply the WHERE search condition.<o:p>
That is the tip so as to build multiple filters on a WHERE clause.<o:p>
<o:p>
<o:p>
<o:p>
<o:p>
About fhunth
Recently, I took a job as a C# senior developer for Huddle Group (http://www.huddle.com.ar/)
Contact me at:
-fhunth@hotmail.com
-fernando@huddle.com.ar
Visit my blog at:
http://msdevelopers.blogspot.com/