Declare Variables / Case Logic / Display Variable 1

[tqeonline]

Hey guys i have a question.

Is there a way to in the Select statement declare variables that equal a select statement and then do case logic on that variable and depending on the case logic increment another variable.

Ex.

Declare @Variable String
Declare @Health Integer
Select [Column1], 

SET @Variable = (SELECT STATEMENT)

Case @Variable = 0 
 @Health = @Health + 1
Case @Variable = 1
 @Health = @Health + 2
End Case

@Health as 'Health'

What that does it sets a variable and then increments the health based off that variable.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

Case statements are used for data, not programming logic. It's a subtle yet important distinction.

If/else If/Else is used to control programming logic, so you could do this...

Declare @Variable String
Declare @Health Integer
Select [Column1], 

SET @Variable = (SELECT STATEMENT)

If @Variable = 0 
   Set @Health = @Health + 1
Else If @Variable = 1
   Set @Health = @Health + 2

Select @Health as 'Health'

You could also do this:

Declare @Variable String
Declare @Health Integer
Select [Column1], 

SET @Variable = (SELECT STATEMENT)

Set @Health = Case When @Variable = 0 
                        Then @Health + 1
                   When  @Variable = 1
                        Then @Health + 2
                   Else @Health
                   End

Select @Health as 'Health'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom