Behzad Behzad - 7 months ago 11
SQL Question

how to avoid repeating multiple selects and inner joins and apply the where clause when its necessary?

In my stored procedure, i am having several parameters which in case if one of them is filled some of them will be ignored and vise versa.

create procedure FlightReservations
@resDate date = getdate,
@fromdate date = null,
@todate date = null,
-- few more parameters
as
begin
if(@fromdate != null)
begin
--it has more inner joins
select * from tbl1 inner join tbl2 on tbl1.id=tbl2.id where tbl1.fromDate=@fromDate
end
else
begin
select * from tbl1 inner join tbl2 on tbl1.id=tbl2.id where tbl1.date=@resDate
end
end


how can i avoid repeating the

select * from tbl1 inner join tbl2 on tbl1.id=tbl2.id


from my code, and apply the where clause on my result table?
what i need is have my result set in something like temporary table and select from the temporary table. but since i do not want to use the temporary table what is the alternative way.(because in it contains several fields and using the temporary table is not a good option)

Answer

Note that checking against null should be used as IS NULL or IS NOT NULL, since NULL is not a value.

This should be what you are looking for

SELECT * FROM tbl1 
INNER JOIN tbl2 
ON tbl1.id = tbl2.id 
WHERE (@fromdate IS NOT NULL AND tbl1.fromDate = @fromDate)
OR (@fromdate IS NULL AND tbl1.date = @resDate)