IIf function problem in 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)

IIf function problem in stored procedure

Author

Topic

mcordewener
Starting Member

19 Posts

Posted - 2005-10-10 : 09:11:59

Hi,

I am trying to convert a MSAccess query to a stored procedure in SQL Server. And somehow the SQL server doesn't approve my iif statement what do I do wrong?

CREATE PROCEDURE [dbo].[X450 - Delete Te Vroege Berichten] AS
UPDATE tblCRM_All SET tblCRM_All.[04x] = IIf(tblCRM_All.[04x] < tblCRM_All.[01x], Null, [04x]), tblCRM_All.[08x] = IIf(tblCRM_All.[08x] < tblCRM_All.[01x],Null,[08x]), tblCRM_All.[09x] = IIf(tblCRM_All.[09x] < tblCRM_All.[01x],Null,[09x]), tblCRM_All.[150] = IIf(tblCRM_All.[150] < tblCRM_All.[01x],Null,[150]), tblCRM_All.[210] = IIf(tblCRM_All.[210] < tblCRM_All.[01x],Null,[210])
From tblCRM_Al
WHERE ((Not (tblCRM_All.[01x]) Is Null));
Go

Greetz,

Marcel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 09:15:42

Replace IIf(tblCRM_All.[04x] < tblCRM_All.[01x], Null, [04x])

by

Case When tblCRM_All.[04x] < tblCRM_All.[01x] then Null else [04x] end

Madhivanan

Failing to plan is Planning to fail

mcordewener
Starting Member

19 Posts

Posted - 2005-10-10 : 09:23:58

Thx, it works

greetz,

Marcel Cordewener

quote:
Originally posted by madhivanan

Replace IIf(tblCRM_All.[04x] < tblCRM_All.[01x], Null, [04x])

by

Case When tblCRM_All.[04x] < tblCRM_All.[01x] then Null else [04x] end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources