Access Stored Procedure to SQL Stored Procedure

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

SQL Server Development (2000)

Access Stored Procedure to SQL Stored Procedure

Author

Topic

MrWombat
Starting Member

11 Posts

Posted - 2003-05-22 : 03:31:07

Hi all i have the following access stroed procedure. I am new to SQL Server and am not too sure how to write it. It is having problems with the IIf statements is there a way of getting the same functionality in SQL Server?

SELECT
tblEstates.est_Id,
tblEstates.est_Name,
tb_Contracts.ctr_FileID,
tblContracts.ctr_Stage,
tblContracts.ctr_SubStage,
[ctr_Stage] & [ctr_SubStage] AS Stage,
tblContracts.ctr_LotNumber,
tblContracts.ctr_Street,
tblContracts.ctr_DateProcessed,
tblContracts.ctr_ActualPrice,
tblAgents.agt_Name AS AgtFee,
tblFeesDue.fee_Amount,
tblAgents_1.agt_Name AS Vendor,
tblContracts.ctr_PurchaserAddressee,
tblContracts.ctr_ContractStatus,
tblContracts.ctr_SettlementProcessed,
tblStages.stg_Stage,
IIf([tblAgents].[agt_id]=57.[fee_Amount],0) AS PeetTot,
IIf([tblEstates].[est_State]='VIC',[PeetTot],0) AS PeetVic,
IIf([tblEstates].[est_State]<>'VIC',[PeetTot],0) AS PeetOther,
tbl_Contracts.ctr_ActualSettlement
FROM (tbl_Estates
INNER JOIN tbl_Stages ON tbl_Estates.est_Id = tbl_Stages.stg_Estate)
INNER JOIN ((tbl_Contracts
INNER JOIN tbl_Agents AS tbl_Agents_1 ON tbl_Contracts.ctr_Vendor = tbl_Agents_1.agt_Id)
INNER JOIN (tbl_Agents
INNER JOIN tbl_FeesDue ON tbl_Agents.agt_Id = tbl_FeesDue.fee_Agent) ON tbl_Contracts.ctr_FileID = tbl_FeesDue.fee_FileId) ON (tbl_Stages.stg_SubStage = tbl_Contracts.ctr_SubStage) AND (tbl_Stages.stg_Stage = tbl_Contracts.ctr_Stage) AND (tbl_Stages.stg_Estate = tbl_Contracts.ctr_Estate)
WHERE (((tbl_Contracts.ctr_ContractStatus)=3));

Cheers
The Wombat

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-22 : 03:39:09

It's called CASE in TSQL. Look it up in BOL for syntax!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-22 : 03:48:50

quote:
[ctr_Stage] & [ctr_SubStage] AS Stage

This line will change to ctr_Stage +(plus sign) ctr_SubStage AS Stage, if they are both strings, or else you will need to cast any integer values as strings.

quote:
IIf([tblEstates].[est_State]='VIC',[PeetTot],0) AS PeetVic,

Like Andraax suggested, you can use the CASE expression here, something like :
CASE tblEstates.est_State WHEN 'VIC' THEN PeerTot ELSE 0 END

quote:

tblStages.stg_Stage,
IIf([tblAgents].[agt_id]=57.[fee_Amount],0) AS PeetTot,
IIf([tblEstates].[est_State]='VIC',[PeetTot],0) AS PeetVic,

What in the world is that?! Does this work?

OS

Edited by - mohdowais on 05/22/2003 03:58:37

MrWombat
Starting Member

11 Posts

Posted - 2003-05-22 : 03:55:34

This is someone elses code that i have to convert. But yeah the report prints fine from this .. well i guess it does i dont know anything about this business. Thanks for the CASE statement when i tried using IF statements in the WHERE clause before i was told that was wrong and it wouldnt work. But hey CASE works so thanks. :)

MrWombat
Starting Member

11 Posts

Posted - 2003-05-22 : 03:57:23

Actually now i look at it this is a typo "57.[fee_Amount" should be a , if i read it correctly.

MrWombat
Starting Member

11 Posts

Posted - 2003-05-22 : 04:18:36

Just as a matter of interest what is the syntax for NOT in TSQL

eg.

CASE tblEstates.est_State WHEN NOT 'VIC' THEN PeerTot ELSE 0 END AS PeetOther

Is there a reference on the web with the TSQL syntax?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-22 : 06:42:12

Try this:

[url]http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/portal_7ap1.htm[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources