SQL - Set Value to a field

I have a field that I need to set a value to with SQL.  Basically the syntax is

IF F1 == NULL Then
   IF  (F2 == NULL or LEN(F2) = 0)) Then
       F3 = 2
    ELSE
       F3 = 1
    END IF
END IF

I've been trying to figure out how to write the above in a case statement.
LVL 2
CipherISAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
IF IsNull(F1) Then
   IF  (IsNull(F2) or LEN(F2) = 0)) Then
       F3 = 2
    ELSE
       F3 = 1
    END IF
END IF

Open in new window


Please note that null can not be equal to a value. The term "null value" often used is no longer used in the new publications because null is not a value and cannot have a value. The acceptable term is either null or null mark. So, == can be used to test two values which doesn't apply to null mark.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
You can write and IF in a SQL Select Statement?  This is what I came up with.  Does this logic match my IF?  

CREATE Table Temp
(
	F1 VARCHAR(10) NULL,
	F2 VARCHAR(20) NULL
)


SELECT 
F2 = CASE WHEN Temp.F1 IS NULL AND (Temp.F2 = NULL OR Len(Temp.F2) = 0) THEN 2 
	 ELSE 1 
	 END
INTO T2 
FROM Temp

Open in new window

0
Mike EghtebasDatabase and Application DeveloperCommented:
CREATE Table Temp
(
	F1 VARCHAR(10) NULL,
	F2 VARCHAR(20) NULL
)


SELECT 
 CASE WHEN Temp.F1 IS NULL AND (Temp.F2 IS NULL OR Len(Temp.F2) = 0) THEN 2 
	 ELSE 1 
	 END
INTO T2 
FROM Temp

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CipherISAuthor Commented:
Ok, I used the F2 = because I was looking at a script I was rewriting.  I see why you removed it.
0
Mike EghtebasDatabase and Application DeveloperCommented:
it is optional.
0
Ryan ChongCommented:
from your original IF statements, it didn't tell us what should be returned if F1 is not NULL.

if F1 is not NULL, do you want to return the condition as a NULL, or 1 ,etc?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Not very clear but try this one

DECLARE  @Temp Table
(
	F1 VARCHAR(10) NULL,
	F2 VARCHAR(20) NULL
)


SELECT 
F3 = 
CASE WHEN 
T.F1 IS NULL 
THEN
CASE WHEN (T.F2 IS NULL OR Len(T.F2) = 0) 
	THEN 2 
ELSE 1 END
ELSE NULL
END
INTO T2 
FROM @Temp T

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
UPDATE TableName
SET F3 = CASE
            WHEN F1 IS NULL THEN
               CASE 
                  WHEN LEN(ISNULL (F2,'')) = = 0 THEN 2
                  ELSE 1
               END
            ELSE F1
        END

Open in new window

0
Scott PletcherSenior DBACommented:
SELECT
F2 = CASE WHEN Temp.F1 IS NULL
                   THEN CASE WHEN (Temp.F2 IS NULL OR Len(Temp.F2) = 0) THEN '2' ELSE '1' END
                   ELSE '' --??your original code doesn't really show what value this should be
       END
INTO T2
FROM Temp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.