running a case statement to act on the contents of a variable 1

[collierd]

Hello

I am SQL 2000

I am looking to create an SP that takes 5 fields
If field1 has something in it, it select something, else field2 etc (case method)

I currently have the following (where the selects are there for test purposes):

TestProc @field1 int, @field2 int, @field3 int, @field4 int, @field5 int

CASE
 WHEN @FIELD1 IS NOT NULL THEN select @FIELD1
 WHEN @FIELD2 IS NOT NULL THEN select @FIELD2
 WHEN @FIELD3 IS NOT NULL THEN SELECT @FIELD3
 WHEN @FIELD4 IS NOT NULL THEN SELECT @FIELD4
 WHEN @FIELD5 IS NOT NULL THEN SELECT @FIELD5
 else null
END

It doesn't like the case
I can see why but can't work out a better method
Any thoughts?

Thanks

Damian.

 

[bborissov]

SELECT CASE WHEN @FIELD1 IS NOT NULL
                 THEN @FIELD1
            WHEN @FIELD2 IS NOT NULL
                 THEN @FIELD2
            WHEN @FIELD3 IS NOT NULL
                 THEN @FIELD3
            WHEN @FIELD4 IS NOT NULL
                 THEN @FIELD4
            WHEN @FIELD5 IS NOT NULL
                 THEN @FIELD5
            ELSE NULL END AS TestMe

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[collierd]

Fantastic, thanks for that