Conditional Statements in Stored Procedure Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional Statements in Stored Procedure Problem

Author

Topic

MrWombat
Starting Member

11 Posts

Posted - 2003-05-15 : 02:28:51

Hi All
Well i am a bit of a newbie to this whole stored procedure thing. I have the following procedure :
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
I was hoping it would append the where clause to the end of the procedure. but it will not accept this. Is there a way to append it so it adds the where clause that is appropriate for the selection.
Cheers in Advance

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-15 : 03:39:37

Hello MrWombat,

You cannot use the if statement in the middle of another statement like that. You have two options here:

1. Use if, but split the whole statement:

if <condition>
select ...
from ...
where ...
else
select ...
from ...
where ...
end

2. Look into the CASE statement. (This is what I would recommend)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-15 : 03:52:07

You could do something like this:

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)
WHERE (tblContracts.ctr_ContractStatus=3)
AND( tblContracts.ctr_ActualSettlement > @SDate
AND tblContracts.ctr_ActualSettlement < @EDate)
AND ( tblEstates.est_Name = COALESCE(NULLIF(@Estate, 0),tblEstates.est_Name)))
END

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-15 : 03:57:21

No don't do that it's not the equivalent of the statement above... Look at this part:

quote:
AND ( tblEstates.est_Name > 0)

MrWombat
Starting Member

11 Posts

Posted - 2003-05-15 : 04:36:20

Cheers Guys.. but unfortunetly if the select statement is inside aconditional statement VS.NET & Crystal Reports cannot see the selected fields for use in the report. I changed it around a bit and added another two parameters
Like so
CREATE PROCEDURE SP_RPT_SettlementsByProject_4
@SDate DateTime,
@EDate DateTime,
@EstateBegin Numeric,
@EstateEnd Numeric,
@StageBegin Numeric,
@StageEnd Numeric
AS
SELECT tblEstates.est_Id,
tblContracts.ctr_ActualSettlement,
tblEstates.est_Name,
tblStages.stg_Stage,
tblContracts.ctr_FileID,
tblContracts.ctr_LotNumber,
tblContracts.ctr_Street,
tblContracts.ctr_DateAcceptance,
tblContracts.ctr_PurchaserAddressee,
tblContracts.ctr_ActualPrice,
tblContracts.ctr_OriginalSettlement,
tblLUContractStatus.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 tblLUContractStatus ON tblContracts.ctr_ContractStatus = tblLUContractStatus.sta_Id) ON (tblStages.stg_SubStage = tblContracts.ctr_SubStage) AND (tblStages.stg_Stage = tblContracts.ctr_Stage) AND (tblStages.stg_Estate = tblContracts.ctr_Estate)
WHERE (tblContracts.ctr_ContractStatus=3)
AND( tblContracts.ctr_ActualSettlement > @SDate AND tblContracts.ctr_ActualSettlement < @EDate)
AND ( tblEstates.est_Id >= @EstateBegin AND tblEstates.est_Id <= @EstateEnd)
AND (tblStages.stg_Stage >= @StageBegin AND tblStages.stg_Stage <= @StageEnd)
ORDER BY tblEstates.est_Id, tblContracts.ctr_ActualSettlement
GO

I just have to be more specific in the parameters i send it. Luckily i know the upper bounds of the Estate Id and stages
Thanks again

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-15 : 04:51:16

Don't know about vs.net, but CR should definately see the fields if you send in a correct set of parameters...

MrWombat
Starting Member

11 Posts

Posted - 2003-05-15 : 04:58:36

What i mean is in Crystal reports when you select the database fields and choose the SP it does not show the selected fields from the stored procedure for you to drag and drop onto you report.
So maybe CR in VS.net works differently

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources