Conditional Stored Procedures Problems

[MrWombat]

Hi all
I have a stored procedure that relies on a the condition of the parameters that are passed as to what is selected. I need to show the selected fields outside of a conditional statement so they can be read by VS.NET Crystal Reports. But it is not accepting the following code.
CREATE PROCEDURE SP_RPT_SettlementsByProject
@SDate Date,
@EDate Date,
@Estate Numeric,
@Stage Numeric
AS
SELECT tbl_Estates.est_Id,
tblContracts.ctr_ActualSettlement,
tblEstates.est_Name,
tblStages.stg_Stage,
[stg_Stage] & [stg_SubStage] AS Stage,
tblContracts.ctr_FileID,
tblContracts.ctr_LotNumber,
tblContracts.ctr_Street,
tblContracts.ctr_DateAcceptance,
tblContracts.ctr_PurchaserAddressee,
tblContracts.ctr_ActualPrice,
tblContracts.ctr_OriginalSettlement,
tblContractStatus.sta_Description,
tblContracts.ctr_ContractStatus,
tblContracts.ctr_OriginalPrice
FROM (tblEstates INNER JOIN tblStages ON tblEstates.est_Id = tblStages.stg_Estate) INNER JOIN (tblContracts LEFT JOIN tblContractStatus ON tblContracts.ctr_ContractStatus = tblContractStatus.sta_Id) ON (tblStages.stg_SubStage = tblContracts.ctr_SubStage) AND (tblStages.stg_Stage = tblContracts.ctr_Stage) AND (tblStages.stg_Estate = tblContracts.ctr_Estate)
if (@Estate = 0)
begin
WHERE (tblContracts.ctr_ContractStatus=3)
AND( tblContracts.ctr_ActualSettlement > @SDate
AND tblContracts.ctr_ActualSettlement < @EDate)
AND ( tblEstates.est_Name = @Estate)
end
else
begin
WHERE (tblContracts.ctr_ContractStatus=3)
AND( tblContracts.ctr_ActualSettlement > @SDate
AND tblContracts.ctr_ActualSettlement < @EDate)
AND ( tblEstates.est_Name > 0)
end
Is there a way of appending the where clause to the select statement?
Cheers in advance

 

[plantj]

Try this

where
(@Estate=0
and (tblContracts.ctr_ContractStatus=3)
AND( tblContracts.ctr_ActualSettlement > @SDate
AND tblContracts.ctr_ActualSettlement < @EDate)
AND ( tblEstates.est_Name = @Estate))
or
((tblContracts.ctr_ContractStatus=3)
AND( tblContracts.ctr_ActualSettlement > @SDate
AND tblContracts.ctr_ActualSettlement < @EDate)
AND ( tblEstates.est_Name > 0))