for loop

HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;

The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?

for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @.AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@.AcctNum);
set @.Cntr=@.Cntr+1
}


I duno its just my guess, can any one help me out. @.Cntr and @.Acctnum are declared
in the beginnning.

tksDECLARE @.cntr int
SELECT @.cntr=0
WHILE @.cntr < 1000
BEGIN
SELECT @.cntr = @.cntr + 1
-- Other statements
END
SELECT @.cntr|||Brett

thanks for the help, look at wut I did. first thing it is not sure that
it is 1000 records. So I counted total num of records and did looping
with that count. But the problem is at the select stmt (in the loop)
the stmt gives 1000 (assume) records, thus 1000 values to the
the variable lngAlsHeadrID, where in the loop we are taking each
value and then setting it to the variable @.AcctNum. Hope u got my ques.
My answer should be like this, I should take each value of
lngAlsHeadrID set it to @.Acctnum and then pass it thru sproc and then
take the second value n so on.......

set @.RowCount = select count(lngALSHeadrID) from ALSHEADR where
lngFedTaxID>0 and lngFedTaxID<999999999 }

WHILE @.cntr < @.RowCount1
BEGIN
declare curHeader cursor dynamic for
select lngALSHeadrID from ALSHEADR where lngFedTaxID>0
set @.AcctNum = lngAlsHeadrID
Exec ALSHeadr2Policy(@.AcctNum)
SELECT @.cntr = @.cntr + 1
END
SELECT @.cntr

Can you suggest me??

Thanks alot|||Just to be picky, I would substitute SET for SELECT in Brett's code, 'cause MS says it's better to, and I do everything they say 8-}|||How big is ALSHeadr2Policy?

Can you post it?

I'd rather give you a set based solution....|||Does smell like a join waiting to happen, doesn't it?|||Originally posted by bpdWork
Just to be picky, I would substitute SET for SELECT in Brett's code, 'cause MS says it's better to, and I do everything they say 8-}

I seen huge global wars about that...WHERE specifically did yo see that?

And what would you prefer

SET @.a = 1
SET @.b = 1
SET @.c = 1
SET @.d = 1
SET @.e = 1
SET @.f = 1

Or

SELECT @.a = 1, @.b = 1, @.c = 1, @.d = 1, @.e = 1, @.f = 1

Wouldn't you think 1 op is better than 6?|||Brett

Thats too big. It has several for loops, while loops and all.
If I can get the idea of syntax for this for loop then I can try to
solve that sproc. It is around 500 lines, just donn wann to confuse you.

Any more suggestions for the above ques.

Thanks|||I got it from BOL under SELECT @.local variables:
"It is recommended that SET @.local_variable be used for variable assignment rather than SELECT @.local_variable. For more information, see SET @.local_variable."

Actually, I think it ends up being 6 ops anyway. I guess it depends on whether you are in the "1 operation per line" camp or not.

I fall somewhere in between: One declare statement, new line for each variable.|||Just out of curiousity, could you pst one of the loops from the big sp?

-b|||Wait a minute, but isn't kir441 talking about a cursor? Check BOL for example of cursor declaration and usage.|||Rdja

Even I got the same idea. Whether I can use a cursor instead of that loop.??

bdp: Here is the for loop

for forIns as curIns dynamic scroll cursor for
select decAccountNum as AccountNum,strInsType,intTypeCode,strVendCode,
datLoanEnds,intTerm,curPremium,strPremType,curFace Amt,strPolicyNum, datStarted,datPlaced,datCanceled,strInsID,strAutoC ancel,datAutoCanceled
from "DBA".ALSINSUR where ALSINSUR.decAccountNum=AcctNum do
set PolicyNum=decAccountNum||'-'||strInsType||'-'||intTypeCode;
set Premium=curPremium;
---------- Make sure we have a product
set ProductID=null;
select MIN(lngProductID) into ProductID from "dba".Products
where strShortCode=strInsType and strVendorCode=strVendCode and lngVendorID=18; %% FTB ALS System
if ProductID=null or ABS(0||ProductID)<1 then
--Add Product to Product Table (ADD LATER)
call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR','Ne ed to Add'
||strInsType||'-'||strVendCode||' to the Products tables.')
end if
;
if ProductID=null or ABS(0||ProductID)<1 then -- Policy will not be added
call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR','Il legal ProductID '
||strInsType||'-'||strVendCode||' for PolicyNum='||PolicyNum)
else
-- We have a Person, Customer and Product that are setup correctly
set PolicyID=null;
select MIN(lngPolicyID) into PolicyID from "DBA".Policies
where strPolicyNumber=PolicyNum and lngProductID=ProductID;
set bForced=0;
set PayModeID=8; -- Single Payment
set PayMethID=3; -- Included in Loan Amout
if strInsType='NIU' then -- Loans that premiums funded as part of the loan
set bForced=1
end if
;
set RefEmpID=null;
set BranchID=null;
set AgentID=null; -- put in default AgentID of Default Record
select MIN(lngEmployeeID) into RefEmpID from "DBA".Employees where lngFTBEmpID=lngOfficerID;
select MIN(lngBranchID) into BranchID from "DBA".Branches where lngRespCntr=intRespCenter;
if PolicyID=null or ABS(0||PolicyID)<1 then -- Need to add Policy
insert into "DBA".Policies(lngBranchID,lngReferEmpID,lngCustomerID, lngPmntModeID,
lngPmntMethID,lngProductID,strPolicyNumber,strStat us,datOrigEffective,
datEffective,datRenewal,curPremium,lngAgentID,blnF orced) values(
BranchID,RefEmpID,CustID,PayModeID,PayMethID,Produ ctID,PolicyNum,'IF',
datStarted,datPlaced,datExpDate,Premium,AgentID,bF orced);
if sqlstate<>'00000' then
call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR','Ca nnot Add Policy Record for PolicyNum='
||PolicyNum||' Loan # '||AcctNum) --else call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','TEST','Add ed PolicyNum='||PolicyNum)
end if
else -- Update the current Policy
update "DBA".Policies set lngBranchID=BranchID,lngReferEmpID=RefEmpID,
lngCustomerID=CustID,lngPmntModeID=PayModeID,lngPm ntMethID=PayMethID,
lngProductID=ProductID,strPolicyNumber=PolicyNum,s trStatus='IF',
datOrigEffective=datStarted,datEffective=datPlaced ,datRenewal=datExpDate,
curPremium=Premium,lngAgentID=AgentID,blnForced=bF orced,datLastStamp=current timestamp
where lngPolicyID=PolicyID and lngProductID=ProductID --call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','TEST','Upd ated PolicyNum='||PolicyNum||' ProductID:'||ProductID)
end if
end if -- ProductID = Null
end for -- Insurances
end if -- CustID
end if -- PersonID
end for; -- ALSHeadr's

ignore those sybase commands as we can change that stuff.
but this is one of the for loops in the sproc.

Any suggestions.

Tks|||declare @.a int
declare @.a1 int
declare @.a2 int
declare @.a3 int
declare @.a4 int
declare @.a5 int
declare @.a6 int
declare @.cntr int

set @.cntr=0

while @.cntr < 100000
begin
set @.a = @.cntr
set @.a1 = @.cntr
set @.a2 = @.cntr
set @.a3 = @.cntr
set @.a4 = @.cntr
set @.a5 = @.cntr
set @.a6 = @.cntr
set @.cntr = @.cntr+1
end

EXECUTION DURATION : 1.28 seconds

--

declare @.a int
declare @.a1 int
declare @.a2 int
declare @.a3 int
declare @.a4 int
declare @.a5 int
declare @.a6 int
declare @.cntr int

set @.cntr=0

while @.cntr < 100000
begin
select @.a = @.cntr, @.a1 = @.cntr, @.a2 = @.cntr, @.a3 = @.cntr, @.a4 = @.cntr, @.a5 = @.cntr, @.a6 = @.cntr, @.cntr = @.cntr+1
end

EXECUTION DURATION: 0.33 seconds|||Try This

declare @.i int
select @.i = id from sysobjects)
select @.i
go
declare @.i int
set @.i = (select id from sysobjects)
select @.i
go

SELECT @.local_variable is usually used to return a single value into the variable. It can return multiple values if, for example, expression is the name of a column. If the SELECT statement returns more than one value, the variable is assigned the last value returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

Use the SET statement to assign a value that is not NULL to a declared variable. The SET statement that assigns a value to the variable returns a single value.

BTW I prefer
SELECT @.local_variable|||Yeah me too...but I confirmed that the SET execution is faster (for 1000 iterations) by about 4000 milliseconds...

careful...don't blink...|||Oh, and btw your code is either Oracle or udb...isn't it...

||, or the concat function isn't available in SQL Server...they use +

And your reference to CALL makes me think it's UDB|||I'm not that picky, and I'm not trying to start something here. I think performace-wise, there's probably very little difference (a few blips here or there).

I use SET when I am assigning a fixed value (SET @.x = 5) and SELECT if I am querying for a value (SELECT @.x = mynum FROM mytable).

My rule is just a personal thing, so my code is more readable to me.|||Originally posted by bpdWork
SELECT if I am querying for a value (SELECT @.x = mynum FROM mytable).


Well Don't you have to?

USE Northwind
GO

DECLARE @.x varchar(10)

SELECT TOP 1 @.x = CustomerID FROM Orders

SELECT @.x
GO

DECLARE @.x varchar(10)

SET TOP 1 @.x = CustomerID FROM Orders

SELECT @.x
GO|||For SELECT, yes, but I don't *NEED* to use SET, I just do.|||Weren't we taling about for loops?