August 1, 2005 at 11:17 am
Imma try to be clear and will show a table =) Sorry to everyone for being not clear in the past.
I have such table: http://img170.imageshack.us/img170/4810/temp12nv.jpg
I'm trying to accomplish procedure that would insert values into column SApproved. So I have next variables passed into procedure @LeaveID, @RegFee, @ETFee, @MiscFee, @LodgingFee, @FoodFee
So how can i match it? I mean how can I make RegFee go into a row with Name Registration Fee? And etc?
August 1, 2005 at 11:30 am
Simple thing like that:
UPDATE Leave_Professional_Funds SET [SApproved] = @RegFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Registration Fee"
UPDATE Leave_Professional_Funds SET [SApproved] = @ETFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Mode of Travel"
UPDATE Leave_Professional_Funds SET [SApproved] = @MiscFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Misc Cost"
UPDATE Leave_Professional_Funds SET [SApproved] = @LodgingFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Lodging Cost"
UPDATE Leave_Professional_Funds SET [SApproved] = @FoodFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Food Cost"
Doesn't work for some reason. (Invalid Column name 'Misc Cost', 'Loding Cost' and etc.)
August 1, 2005 at 1:27 pm
got it to work.
August 1, 2005 at 1:33 pm
First:
You should replace your " for '
Second:
all can be replaced using a case statement that way you make a single pass to the table:
UPDATE Leave_Professional_Funds SET [SApproved] = case when [Name] = 'Registration Fee' then @RegFee
when [Name] = 'Mode of Travel' then @ETFee
when [Name] = 'Misc Cost' then @MiscFee
when [Name] = 'Lodging Cost' then @LodgingFee
when [Name] = 'Food Cost' then @FoodFee
else [SApproved] end
WHERE [LeaveRequestID] = @LeaveID
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
