July 31, 2008 at 3:07 pm
Hi,
I am trying to use an if-else statement in an stored proc, so my question is can we compare two remporary variables in an if-else clause..please help me. I am trying to do
if @= @b-2
begin
statements
end
else
statements
when i am trying to run this, it is not showing any errors but the if-else statement is not getting executed.
Regards
Murali
July 31, 2008 at 3:20 pm
What do you mean the IF ELSE is not getting executed and how do you know?
Are you sure the variables are being initialized?
You can compare 2 variables like this:
[font="Courier New"]DECLARE @a INT, @b INT
SET @a = 2
SET @b = 3
IF @a > @b
BEGIN
PRINT 'a > b'
END
ELSE
BEGIN
PRINT 'b > a'
END[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 3:23 pm
select @p = leg_id, @pall= pallets_amt, @su = lean_payable_amt from @TempIndividualFuel where rowid = @index
while @ind <= @TotalStops
begin
select @pa = leg_id,@pallet= pallets_amt, @sum = lean_payable_amt from @TempIndividualFuel2 where rowid = @ind
if @p = @pa
begin
set @pallets=@pallets + @pallet
set @Tsum = @Tsum + @sum
select @cc = pallets_amt / @pallets from @TempIndividualfuel where rowid=@index
set @newAmmount = @cc * @Tsum
update @TempIndividualFuel2 set lean_payable_amt = @newAmmount where rowid=@index
set @ind = @ind +1
end
this is what actually i am trying to do...and now the control is not going into the if clause
July 31, 2008 at 3:38 pm
The code you posted is incomplete. You are missing at least 1 END and you are not explaining what you want the code to do. If you look at the links in my signature and post accordingly you will get better answers faster.
What you are currently doing is likely not going to scale well because of the looping and, if we see what you are trying to do, someone here can probably offer you a simple set-based solution that will eliminate the looping and the need for the variables and IF. Based on what I can see in your limited code example it looks like joining the 2 table variables and doing the update is possible, but it is hard to tell because you use variables that are never defined in your example.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 3:52 pm
thanks, So I am trying to update my main temporery table by actually caluculating thr freight cost, So in this process i am trying to get the data in an individual temp table of each field and then comparing the first leg_id from thr first table and the first leg_id of the second table and if they are equal i am updating the table according to the given calculation, if they are not equal incrementing the loop of the second table. basically i have diffirent item id's for the same leg id do..if this is the case i want to allocate the money based upon how many items are shipped for an particular leg id..below is the code
select @TotalStops = count(*) from @TempIndividualFuel
while @index <= @TotalStops
begin
select @p = leg_id, @pall= pallets_amt, @su = lean_payable_amt from @TempIndividualFuel where rowid = @index
while @ind <= @TotalStops
begin
select @pa = leg_id,@pallet= pallets_amt, @sum = lean_payable_amt from @TempIndividualFuel2 where rowid = @ind
if @p = @pa
begin
set @pallets=@pallets + @pallet
set @Tsum = @Tsum + @sum
select @cc = pallets_amt / @pallets from @TempIndividualfuel where rowid=@index
set @newAmmount = @cc * @Tsum
update @TempIndividualFuel2 set lean_payable_amt = @newAmmount where rowid=@index
set @ind = @ind +1
end
else
print @pallet
print @pallets
print @Sum
end
set @p=0
set @pa=0
set @pall=0
set @su=0
set @Tsum=0
set @sum=0
set @pallet =0
set @pallets = 0
end
the @tempindividualfuel and @tempindivdualfuel2 have the same records
August 5, 2008 at 7:41 am
thanks..! i got it. I have another problem now.. how can we pass a perameter to call a function from a stored procedure.. please help
August 6, 2008 at 1:57 am
It's still really difficult to tell what's supposed to be happening here because the use of variables doesn't necessarily make any sense. It would help people a great deal if you could provide the structure of these two tables and some sample data to work with.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 6, 2008 at 6:50 am
you could throw in a few judiciously placed PRINT statements, as already suggested
don't you have a debugger for your stored procedures? if not in SQL Server itself then in Visual Studio?
August 6, 2008 at 6:55 am
to clarify passing a parameter from a stored procedure into a function, especially if both parameters have the same name, DECLARE a variable of the same type and set it to the stored procedure variable
if the variables have different names then they can be used as is
CREATE PROCEDURE spSomething
@var1 INT
AS
BEGIN
SELECT fnSomethingElse(@var1) AS SomethingElse, etc.
for scalar variables
or
SELECT col1, col2, etc FROM fnTable(@var1)
for table variables
END
August 6, 2008 at 10:02 am
You should probably familiarize yourself with the debugger and step through the code. It's very good for examining variables in stored procedures.
You may very well have one of the values as a NULL in the comparison in the IF statement. NULL never tests true when compared to anything - even another NULL.
Todd Fifield
August 7, 2008 at 2:10 am
If the two temp tables have the exact same data as you say - this seems like a waste of time?
You're essentially comparing a table to it's self and performing some math against it. You could easily do all of that with a simple select, and use built in aggregates.
As it is, much of the work you're doing can be eliminated using the SUM() function to perform your math, and matching up the records using joins and where clauses. something along the lines of this:
select @Palets=sum(t2.pallets_amt), @Tsum=sum(t2.lean_payable_amt), @cc=(t1.pallets_amt/SUM(t2.pallets_amt)),
@newAmount=(t1.pallets_amt/SUM(t2.pallets_amt))*sum(t2.lean_payable_amt)
from @TempIndividualFuel t1 inner join @TempIndividualFuel2 t2 on t1.leg_id=t2.leg_id
I think to achieve the update (since you're only updating one field) you could do it really simple like this:
update @TempIndividual2 set lean_payable_amt=(t1.pallets_amt/SUM(t2.pallets_amt))*sum(t2.lean_payable_amt) from @TempIndividualFuel t1 inner join @TempIndividualFuel2 t2 on t1.leg_id=t2.leg_id
That's what I see based on the code you put up there with your loops, etc.
This is just a quick glance, and it is pretty late.
Post your entire stored proc, and i'll flip through it. Guarentee there's a faster/easier way of doing things then the loops you're using now though.
August 7, 2008 at 6:40 pm
You don't have begin-end delimiters after the else. So only the first print statement (print @pallet) is getting executed when the ifcondition is false. The statements from the second print statement (print @pallets) on are always getting executed because they are outside the scope of the if-else construction.
I don't understand your other question -- how do you pass a parameter to call a function from a stored procedure? Perhaps an example of what you are trying to do would help.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 7, 2008 at 7:57 pm
murali_babuin (8/5/2008)
thanks..! i got it. I have another problem now.. how can we pass a perameter to call a function from a stored procedure.. please help
Two way street here... if you found your own answer, would you mind posting it, please? Thanks...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply