Newbie- variables in stored procedure 1

[thminer]

I have a problem with defining variables in my stored procedure. i need the proc. to pull in a bunch of data and do some calc's with about 10 variables. the variable @productivity. is a series of if then statements and calculations. i do not know the proper syntax for this variale. please help!!!

(If {EM.emEmployee} = ""
Then
If @Paid Hours = 0
Then 0
Else
If {EM.emSelect1 = "Salary"
Then
Sum @DirectHours, EM.emEmployee - 0.0 % @Weeks Worked * 40 - Sum @Vacation Used, EM.emEmployee
Else
Sum @DirectHours, EM.emEmployee % @Paid Hours

Else
If @Paid Hours = 0
Then 0
Else
If EM.emSelect1 = "Salary"
Then
Sum @DirectHours, EM.emEmployee - EM.emSelect21 % @Weeks Worked * 40 - Sum @Vacation Used, EM.emEmployee
Else
Sum @DirectHours, EM.emEmployee % @Paid Hours
,0

 

[jbenson001]

First declare your variable
Declare @productivity <data type>

To assign a variable
Set @productivity = <value>
OR

Set @productivity = <some col>
From <some table>
Where .....

 

[thminer]

the variable is defined, but the problem i am having is with if then syntax. the value will not be a column, etc., it will be the result of the entire if-then statement posted in my original message. i am not sure how to write that statement in SQL language to define the variable

 

[jbenson001]

If I am following correctly, you need to place the final result of the IF .. above into a variable. As far as I can tell you need to place the assignment statement in each part of your IF...

IF some condition
BEGIN
Do stuff....
Set @productivity = <some value>
END
Else
BEGIN
Do some other stuff
Set @productivity = <some value>
END

 

[thminer]

thanks, i think that does it.

 

[jbenson001]

Great, glad you got it...

Jim