If Statement with many values - SQL Server Forums

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)

If Statement with many values

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-03-29 : 05:23:38

Hi I need to write a if statement in a User defined Function that looks at many values to see if they are not equal to what i've in my conditions,

What am trying to achieve is if ContractDesc is not equal to (M' , ',ADMIN STOP',' NULL/BLANK','FACTURATION PMO','STOP(LEGAL)',' UNK')

set vale to 1.

Here is my sample code am working with,
IF @ContractDesc <> ( 'T&M' , ',ADMIN STOP',' NULL','FACTURATION PMO','STOP(LEGAL)',' UNK')

This does not work, any idea's on how to write this ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-29 : 05:33:49

look for Case When in Book online

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-29 : 05:37:52

Hi. You're comparing a string value to an array, so the result is never going to be true. You can use the IN keyword as follows:

IF @ContractDesc NOT IN ( 'T&M' , ',ADMIN STOP',' NULL','FACTURATION PMO','STOP(LEGAL)',' UNK')
  --Do stuff

It's worth describing what you're doing and posting some code, as it could be that you don't need a UDF at all.

Mark

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-03-29 : 05:48:39

Woops sorry, i did some cleaning up on the code here it is now, working.. let me know thats your thoughs are on the NOT IN statement or would it be better to use a Case. Thanks

CREATE FUNCTION dbo.udf_Voy_Serv_Prod_ind(@Serviceable char(2),@ContractDesc char(25))
RETURNS tinyint
AS

BEGIN

DECLARE @Serv_Prod_ind tinyint

If @Serv_Prod_ind = 'T'
Begin
IF @ContractDesc NOT IN ('T&M' , ',ADMIN STOP' , ' NULL' , 'FACTURATION PMO' , 'STOP(LEGAL)' , ' UNK' )
Begin
Set @Serv_Prod_ind = 1
End
Else
Set @Serv_Prod_ind = 0

Set @Serv_Prod_ind = 1
End
Else
Set @Serv_Prod_ind = 0
RETURN @Serv_Prod_ind
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 05:48:50

>>as am getting a few errors on his ?

What is the error?
Why did you use char(25)?

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-03-29 : 05:52:34

quote:
Originally posted by madhivanan

>>as am getting a few errors on his ?

What is the error?
Why did you use char(25)?

Madhivanan

Failing to plan is Planning to fail

Sorry i cleaned up the code, its now working, i used the char(25) as the values am passing into the function is of that size and all am returning is a 1 or 0 so i used a tinyint for the ruturn value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources