How To use while loop without using a cursor
-
Hi,
I'm writing a stored procedure that needs to evaluate account numbers and update another table based on those account numbers. I'm not clear on the while loop syntax. Here is an example
Lookup table Source Table Acct Values Acct Values 1 15 1 10 2 20 3 20 3 30 4 30 4 40 5 40 5 50 6 50 I need to iterate through the lookup table accounts and within the loop evalute if the account exists in my source table if it does update the value from the lookup table to the source table if the account doesn't exist then update the source value to 0. In VB.NET I would simply create an array and evaluate each account accordingly. However I want to do this in T-SQL without the use of cursors (if possible).
Thanks in advanced
-
I tend to use While loops in my T-SQL code. It's fairly simple. I usually use temp tables to serve as my arrays.
Basically the While loop syntax is the following:
DECLARE @total_count int --number representing the count of data in your temp table
Set @total_count = SELECT COUNT(Acct) FROM Lookup_table
DECLARE @counter1 int --this is the counter
WHILE @counter1 <= @total_count
BEGIN
[YOUR CODE HERE]
SET @counter1 = (@counter1 + 1)
END
-
Hi,
I need to evaluate each account number in the while loop and not just have a counter. My question is how do I evaluate each account. This is what I have so far:
I'm loading the account number into a temp table, I declared a couple of variables one new and one old and what I am trying to do is iterate through the entire account list and evaluate that account and do something with that account. However I'm not sure if my iteration is correct.
Thanks,
SELECT budgetaccountnumber INTO #MyTemp FROM source_budgetaccounts WHERE budgetaccountnumber <> 0
DECLARE @Budgetaccount INT
DECLARE @NewBudgetaccount INT
SELECT @NewBudgetaccount = budgetaccountnumber FROM #MyTemp
WHILE @NewBudgetaccount != @Budgetaccount
BEGIN
IF (SELECT COUNT(gldept) FROM source_overhead_budgetaccounts_by_dept_budgets
WHERE gldept = @GLDept AND budgetaccount = @NewBudgetaccount) > 0
BEGIN
PRINT @NewBudgetaccount
SET @NewBudgetaccount = @Budgetaccount
END
END
-
DECLARE @NextAccount INT SET @NextAccount=0 WHILE @NextAccount IS NOT NULL BEGIN SELECT @NextAccount=MIN(acct) FROM dbo.YourTable WHERE acct > @NextAccount IF @NextAccount IS NOT NULL BEGIN exec dbo.YourProc @NextAccount -- etc END END -
Hello again,
This is the final T-SQL - everything is working fine now
Thanks for all your help
=============================
DECLARE @NextAccount INT
SET @NextAccount=0
WHILE @NextAccount IS NOT NULL
BEGIN
SELECT @NextAccount=MIN(budgetaccountnumber) FROM #MyTemp
WHERE budgetaccountnumber > @NextAccount
IF @NextAccount IS NOT NULL
BEGIN
IF (SELECT COUNT(gldept)
FROM source_overhead_budgetaccounts_by_dept_budgets
WHERE gldept = @OtherVariable AND budgetaccount = @NextAccount) > 0
PRINT @NextAccount
END
BEGIN
IF (SELECT COUNT(gldept)
FROM source_overhead_budgetaccounts_by_dept_budgets
WHERE gldept = @OtherVariable AND budgetaccount = @NextAccount) = 0
PRINT 'Budget accounts na Update to ZERO'
END
END
================================================
-
I may be missing something here, but I think this is the effect you are attempting to achieve
update
Lookup set
[values] = s.[values]
from
(
select l.acct, s.[values]
from
Lookup as l
left
join source as s
on
l.acct = s.acct
where
s.acct is not null) as s
where
s.acct = Lookup.acct
GO
update
Lookup set
[values]
= 0
from
(
select l.acct, s.[values]
from
Lookup as l
left
join source as s
on
l.acct = s.acct
where
s.acct is null) as s
where
s.acct = Lookup.acct
-
Now I'm really getting confused (which is not hard to do
Anyway,
Here is my latest iteration:
DECLARE @NextAccount INT
SET NOCOUNT ON
SET @NextAccount=0
WHILE @NextAccount IS NOT NULL
BEGIN
SELECT @NextAccount=MIN(budgetaccountnumber) FROM BudgetAccounts WHERE Budgetaccountnumber > @NextAccount
IF @NextAccount IS NOT NULL
BEGIN
IF (SELECT COUNT(gldept)
FROM source_overhead_budgetaccounts_by_dept_budgets
WHERE gldept = @GLDEPT AND budgetaccount = @NextAccount) > 0
PRINT 'SELECT COUNT(gldept) FROM source_overhead_budgetaccounts_by_dept_budgets WHERE gldept =' + @GLDEPT + ' AND budgetaccount = ' + CONVERT(VARCHAR,@NextAccount) This print statement is only showing up once which is what I want to happen
BEGIN
UPDATE a SET a.ap1 = b.ap1,a.ap2 = b.ap2,a.ap3 = b.ap3,a.ap4 = b.ap4
FROM source_budgetaccounts a INNER JOIN SOURCE_Overhead_BudgetAccounts_by_Dept_Budgets b
ON a.budgetaccountnumber = b.budgetaccount
WHERE b.gldept = @GLDEPT AND a.budgetaccountnumber = @NextAccount
In the print statement below I expected to see one print statement because only one met the criteria of > 0, however I'm displaying a print statement for all accounts shouldn't I just be seeing one print statement. What in the world am I doing wrong here?
PRINT 'UPDATE a SET a.ap1 = b.ap1,a.ap2 = b.ap2,a.ap3 = b.ap3,a.ap4 = b.ap4 FROM source_budgetaccounts a INNER JOIN SOURCE_Overhead_BudgetAccounts_by_Dept_Budgets b ON a.budgetaccountnumber = b.budgetaccount WHERE b.gldept = ' + @GLDEPT + ' AND a.budgetaccountnumber = ' + CONVERT(VARCHAR,@NextAccount)
END
/* ELSE
BEGIN
UPDATE source_budgetaccounts SET ap1 = 0,ap2 = 0,ap3 = 0, ap4 =0
END */
END
END
Thanks,
-
Hi Again,
I changed the if condition from
IF (SELECT COUNT(gldept)
FROM source_overhead_budgetaccounts_by_dept_budgets
WHERE gldept = @GLDEPT AND budgetaccount = @NextAccount) > 0
to
IF EXISTS(SELECT gldept FROM source_overhead_budgetaccounts_by_dept_budgets WHERE gldept = CONVERT(INT,@GLDEPT) AND budgetaccount = @NextAccount)
This allows the evaluation to occur and return the correct SQL statement.
Thanks,
-
Joe, this problem does not need a cursor. Never use a cursor when a set-based solution is available.
Look at this simple solution.
-- Update the existing accounts in Lookup table with Source values
UPDATE lt
SET lt.Values = st.Values
FROM LookupTable lt
INNER JOIN SourceTable st WHERE st.Acct = lt.Acct
-- Insert the missing accounts, and their values
INSERT Lookup
(
Acct,
Values
)
SELECT st.Acct,
st.Values
FROM SourceTable st
WHERE st.Acct NOT IN (SELECT lt.Acct FROM LookupTable lt)
N 56°04'39.16"
E 12°55'05.25" -
Joe, just in case you did not hear about it,
the name for array in SQL is "table".
You don't need to create arrays, they are already there. Just use it.
_____________
Code for TallyGenerator -
Joe, Peter's set based solution is a much better approach to using a loop of any kind. There are many, many posts and articles on SSC detailing the benefits of set based T-SQL versus loop/iteration based programming. Most examples that use a loop can be re-written as a set based solution that, in terms of performance, will be significantly more efficient.
Also, Sergity is correct, T-SQL does have arrays - table variables.
-
No, I mean tables, not only table variables.
_____________
Code for TallyGenerator -
declare @Lookup table
(
Acctint,
[Values]int
)
declare @Source Table (
Acctint,
[Values]int)
insert into @Lookup values (1,15)
insert into @Source values (1,10)
insert into @Lookup values (2,20)
insert into @Source values (3,20)
insert into @Lookup values (3,30)
insert into @Source values (4,30)
insert into @Lookup values (4,40)
insert into @Source values (5,40)
insert into @Lookup values (5,50)
insert into @Source values (6,50)
select * from @Lookup
select * from @Source
update s
set [Values] = isnull(l.[Values],0)
from @Source s
left join @Lookup l
on s.acct = l.acct
--select * from @Lookup
select * from @Source
-
Thanks for the input. I just wanted to find the most efficient way of being able to iterate through a record of accounts and ensure that I evaluated all the records against a set criteria. I did not use a cursor in this example just a while loop. I went with was most comfortable to me. As you mentioned I should have (and will rewrite my store proc.) used a straight set SQL to do this.
Again thanks for everyone's input
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply