LOOP ON VARIABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

LOOP ON VARIABLE

Author

Topic

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-23 : 05:55:20

Good day,please help me to loop on this script.
i want to loop all the @CSHDRKey from a #temptable then use it as a parameter to run the following script. i want to create loop where
for every CSHRKEY from #temptable then pass it on the following script to get the results,here is my script.

notice the @CSHDRKey i set to static,the result is okie but i want
to basically loop the @CSHDRKEY from #TempTable(Temp tables contains many CSHDRKEY),really appreciate your help,thank you.

--------------Begin ----------------------------------
USE [TMPC]
GO
/****** Object: StoredProcedure [dbo].[RptADBRewardsTEST] Script Date: 04/23/2012 17:39:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-
*/

--exec [RptADBRewardsTEST] '3/1/2012','3/31/2012','2253'
ALTER Procedure [dbo].[RptADBRewardsTEST]

@Startdate datetime
,@Enddate datetime
,@CSHDRKey int

As SET NOCOUNT ON

BEGIN

Set @Startdate ='3/1/2012'
Set @Enddate ='3/31/2012'

DECLARE @StartFolder datetime, @EndFolder datetime, @DaysPassed int,
@CurrYear int, @NoDaysYr int, @MinDate datetime, @BegBal money, @IRate float,
@withTax float, @MinBalance money, @sumNoofDays int,@Month int,@FolderKey int

SELECT @MinBalance = IntOnSavingsMinBalance FROM parameters

SELECT CSHDRKey,TransDate, BeginBal = cast(0 as money),
Debit = SUM(CASE Type WHEN 'D' THEN Amount ELSE 0 END),
Credit = SUM(CASE Type WHEN 'C' THEN Amount ELSE 0 END),
EndBal = cast (0 as money), DaysInterval = cast(0 as money),
ADB = cast(0 as money)
INTO #TempSavings FROM ClientSavingsDTL WHERE CSHDrkey =@CSHDRKey AND TransDate >= @StartDate
AND TransDate < dateadd(day,1,@EndDate)
GROUP BY CSHDrKey, TransDate

SELECT @BegBal = SUM(CASE Type WHEN 'C' THEN Amount ELSE Amount * - 1 END)
FROM ClientSavingsDTL WHERE CSHDrkey = @CSHDRKey
AND TransDate < @EndDate
end
begin
SET @BegBal = ISNULL(@BegBal,0)

INSERT INTO #TempSavings VALUES (@CSHDRKey, dateadd(day,-1,@Startdate), @BegBal,0,0, @BegBal,0,0)

SELECT @MinDate = min(Transdate) FROM #TempSavings

DECLARE @Transdate datetime, @Debit money, @Credit money, @Endbal money, @LastTranDate datetime, @DaysInterval int

SET @LastTranDate = dateadd(day,-1,@StartDate)

DECLARE cur_savings CURSOR FOR SELECT TransDate, Debit, Credit FROM #TempSavings ORDER BY TransDate

OPEN cur_savings
FETCH NEXT FROM cur_savings INTO @TransDate,@Debit, @Credit

WHILE @@FETCH_STATUS = 0
BEGIN

SET @EndBal = @BegBal + @Credit - @Debit

SELECT @LastTranDate = min(TransDate) FROM #TempSavings WHERE CSHDRKey = @CSHDRKey AND TransDate > @TransDate
IF @LastTranDate IS NULL
SET @LastTranDate = @EndDate

SET @DaysInterval = datediff (day, @TransDate, @LastTranDate)
UPDATE #TempSavings SET BeginBal = @BegBal, EndBal = @EndBal,DaysInterval = @DaysInterval
WHERE CSHDrkey = @CSHDRKey AND TransDate = @TransDate

SET @BegBal = @EndBal

FETCH NEXT FROM cur_savings INTO @TransDate, @Debit, @Credit

END

CLOSE cur_savings
DEALLOCATE cur_savings
end

-- compute adb only to those above the minimum balance
UPDATE #TempSavings SET ADB = EndBal*CAST(DaysInterval AS MONEY)/(DateDiff(Day, @StartDate, @EndDate)+1)--/ @NoDaysYr
WHERE EndBal > @MinBalance

SELECT T.CSHDRKey, C.ClientCode, CE.LastName + ', ' + CE.FirstName + ' ' + CE.MiddleName ClientName, C.SavingsCode,
P.SVDesc SavingsName, T.TransDate TranDate, EndBal =
(select top 1 endbal from #Tempsavings ORDER BY TransDate desc),
DateDiff = 0, AveTransBalance = cast(0 as money),
AveDailyBalance = ADB,dateinterval = 0
INTO #FinalDest FROM (SELECT CSHDrkey, SUM(ADB) ADB, max(TransDate) TransDate
FROM #TempSavings GROUP BY CSHDrkey) T INNER JOIN ClientSavings C
ON C.CSHDRkey = T.CSHDRkey LEFT JOIN Clientele CE ON CE.ClientCode = C.ClientCode
LEFT JOIN PrSavings P ON P.Acctcode =C.SavingsCode
WHERE C.SavingsCode = '363'
--END

Select * from #TempSavings
Select * from #FinalDest
drop table #TempSavings
drop table #FinalDest

--------------End-------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:53:45

why do you need to loop? what are you trying to calculate inside loop? is it running count? if yes, it can be implemented using set based logic which would be much more efficient

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-24 : 01:18:49

i need to loop the variable @CSHDRKEY i want to select it from table then pass it into my loop here is my example.

declare @cshdrkey varchar(10)
select cshdrkey into #temp from Client
set @cshdrkey = cshdrkey

DECLARE cur_TEST CURSOR FOR
SELECT CSHDRKEY FROM #temp

OPEN cur_TEST
FETCH NEXT FROM cur_savings INTO @CSHDRKEY

WHILE @@FETCH_STATUS = 0
BEGIN
-do somethine here using @CSHDRKEY

FETCH NEXT FROM cur_TEST INTO CSHDRKEY

END

CLOSE cur_TEST
DEALLOCATE cur_TEST
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 01:24:49

you still didnt answer my question. what are you doing inside loop? why cant it be done in a set based manner?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-24 : 03:53:32

i dont know how to create script on a set base approach. can you please help me,thank you.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 07:13:59

If you can post the DDL of the tables being used, some readily consumable sample data and a precise explanation of the business requirement then we could give you a Set Based solution for it.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:39:47

quote:
Originally posted by julius.delorino

i dont know how to create script on a set base approach. can you please help me,thank you.

first explain what exactly you're trying to do inside update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-25 : 01:33:38

select @Variable from #temp --contains 3 record

-output return from querry above
@variable1
@variable2
@variable3
--------------------------------

create a loop
set to record 1 = @variable1
--do something here

then next record @Variable2
until last record

please help. thank you.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-04-25 : 14:22:55

I think what Visak is saying, is there is almost definitely a set-based approach that is the correct way to accomplish what you are looking to accomplish. However, the posts you put up are very difficult to see what you are looking to do since you are just illustrating a loop without showing how you obtain your data and how it is to be used.

Please post DDL for sample data and a sample of what you are doing with the data and desired end results after your LOOP.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-25 : 21:43:47

here is my sp use to compute for average daily balance.
notice the parameter @CSHDRKey(contains only one record supplied by user).
i want to eliminate the parameter @CSHDRKey in order to have a result base from selection of (select CSHRDKEY from clientsavingsDTL)
after i get the CSHRDKEY then pass it to @CSHRDKEY then apply
the script below then (select CSHRDKEY from clientsavingsDTL) again
until to the last records from clientsavingsDTL.

----------------------------------------------------------------------

USE [TMPC]
GO
/****** Object: StoredProcedure [dbo].[RptADBRewardsTEST] Script Date: 04/26/2012 09:31:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-
*/

--exec [RptADBRewardsTEST] '3/1/2012','3/31/2012','3103'
ALTER Procedure [dbo].[RptADBRewardsTEST]

@Startdate datetime
,@Enddate datetime
,@CSHDRKey int

As SET NOCOUNT ON

BEGIN

DECLARE @DaysPassed int, @NoDaysYr int, @MinDate datetime, @BegBal money, @MinBalance money--,@CSHDRKey int ='2577'

SELECT @MinBalance = IntOnSavingsMinBalance FROM parameters

SELECT CSHDRKey,TransDate, BeginBal = cast(0 as money),
Debit = SUM(CASE Type WHEN 'D' THEN Amount ELSE 0 END),
Credit = SUM(CASE Type WHEN 'C' THEN Amount ELSE 0 END),
EndBal = cast (0 as money), DaysInterval = cast(0 as money),
ADB = cast(0 as money)
INTO #TempSavings FROM ClientSavingsDTL WHERE CSHDrkey =@CSHDRKey AND TransDate >= @StartDate
AND TransDate < dateadd(day,1,@EndDate)
GROUP BY CSHDrKey, TransDate

SELECT @BegBal = SUM(CASE Type WHEN 'C' THEN Amount ELSE Amount * - 1 END)
FROM ClientSavingsDTL WHERE CSHDrkey = @CSHDRKey
AND TransDate < @EndDate
end
begin
SET @BegBal = ISNULL(@BegBal,0)

INSERT INTO #TempSavings VALUES (@CSHDRKey, dateadd(day,-1,@Startdate), @BegBal,0,0, @BegBal,0,0)

SELECT @MinDate = min(Transdate) FROM #TempSavings

DECLARE @Transdate datetime, @Debit money, @Credit money, @Endbal money, @LastTranDate datetime, @DaysInterval int

SET @LastTranDate = dateadd(day,-1,@StartDate)

DECLARE cur_savings CURSOR FOR SELECT TransDate, Debit, Credit FROM #TempSavings ORDER BY TransDate

OPEN cur_savings
FETCH NEXT FROM cur_savings INTO @TransDate,@Debit, @Credit

WHILE @@FETCH_STATUS = 0
BEGIN

SET @EndBal = @BegBal + @Credit - @Debit

SELECT @LastTranDate = min(TransDate) FROM #TempSavings WHERE CSHDRKey = @CSHDRKey AND TransDate > @TransDate
IF @LastTranDate IS NULL
SET @LastTranDate = @EndDate

SET @DaysInterval = datediff (day, @TransDate, @LastTranDate)
UPDATE #TempSavings SET BeginBal = @BegBal, EndBal = @EndBal,DaysInterval = @DaysInterval
WHERE CSHDrkey = @CSHDRKey AND TransDate = @TransDate

SET @BegBal = @EndBal

FETCH NEXT FROM cur_savings INTO @TransDate, @Debit, @Credit

END

CLOSE cur_savings
DEALLOCATE cur_savings
end

-- compute adb only to those above the minimum balance
UPDATE #TempSavings SET ADB = EndBal*CAST(DaysInterval AS MONEY)/(DateDiff(Day, @StartDate, @EndDate)+1)--/ @NoDaysYr
WHERE EndBal > @MinBalance

SELECT T.CSHDRKey, C.ClientCode, CE.LastName + ', ' + CE.FirstName + ' ' + CE.MiddleName ClientName, C.SavingsCode,
P.SVDesc SavingsName, T.TransDate TranDate, EndBal =
(select top 1 endbal from #Tempsavings ORDER BY TransDate desc),
DateDiff = 0, AveTransBalance = cast(0 as money),
AveDailyBalance = ADB,dateinterval = 0
INTO #FinalDest FROM (SELECT CSHDrkey, SUM(ADB) ADB, max(TransDate) TransDate
FROM #TempSavings GROUP BY CSHDrkey) T INNER JOIN ClientSavings C
ON C.CSHDRkey = T.CSHDRkey LEFT JOIN Clientele CE ON CE.ClientCode = C.ClientCode
LEFT JOIN PrSavings P ON P.Acctcode =C.SavingsCode
WHERE C.SavingsCode = '363'
--END

Select CSHDRKey,ClientCode,ClientName,SavingsCode,SavingsName,TranDate,AveDailyBalance
from #FinalDest
drop table #TempSavings
drop table #FinalDest

----------------------------------------------------------------------
RESULT TABLE -OUTPUT

----------------------------------------------------------------------
Cshdrkey|Clientcode|Clientsname|Savingscode|Desc|TranDate|ADB|
3103 RM-001367 GACAYAN, JOYCE J. 363 PAID - UP SHARE CAPITAL - COMMON 2012-02-29 11600.00

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-04-25 : 22:58:52

OK... That is not really what was requested, but I'll try to work with it.

Anyway, I took a look at your code. First the procedure doesn't need a cursor you can use a CTE for the running totals or a cluster table update for much better performance, but that is not the issue you were looking to resolve so I'll skip to the question.

You would want to create a table valued function instead of a procedure to bring all results into a table(CONVERT ALL YOUR TEMP TABLES to TABLE VARIABLES in the current procedure then return the table. SQL MANAGEMENT STUDIO has a template for how to create a table variable function, just use that and paste your code (Changing the #table's to table variables (i.e. tables that begin with @tablename) and it should be ready to go.

Then you can pass it to the function like so

select b.* from
#TEMPTABLE a
cross apply
(Select * from FN_RptADBRewardsTEST('1/1/2011','1/1/2012',a.CSHDRKEY) b

it will run and returned all results for all CSHDRKEY in #TEMPTABLE.

Since you didn't post the ddl and sample data, I don't really want to invest a lot of time in this to recreating what you are doing, but if you decide to post some sample data and DDL and desired results, I'll try to assist more if you get stuck, or someone else on here I am sure will assist as well.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-25 : 23:27:20

Thanks for your help sir. i'll try to use your solution.

julius.delorino
Starting Member

29 Posts

Posted - 2012-04-25 : 23:31:53

here is my DDL of my tableclientsavingsdtl

USE [TMPC]
GO

/****** Object: Table [dbo].[ClientSavingsDTL] Script Date: 04/26/2012 11:31:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ClientSavingsDTL](
[ID] [varchar](60) NOT NULL,
[RefNo] [varchar](15) NOT NULL,
[CSHDRKey] [numeric](9, 0) NOT NULL,
[TransNo] [varchar](10) NOT NULL,
[TransDate] [datetime] NOT NULL,
[Amount] [money] NOT NULL,
[Remarks] [varchar](100) NULL,
[Type] [char](1) NOT NULL,
[TransType] [char](1) NOT NULL,
[Teller] [varchar](16) NOT NULL,
[Workstation] [varchar](50) NOT NULL,
[PrnSlipYN] [bit] NOT NULL,
[PrnPassbookYN] [bit] NOT NULL,
[PrnLedgerYN] [bit] NOT NULL,
[Status] [char](10) NULL,
[Batch] [varchar](10) NULL,
[Time] [datetime] NULL,
[TransDesc] [varchar](4) NULL,
[Line] [smallint] NULL,
[Page] [smallint] NULL,
[Book] [smallint] NULL,
[CashAccnt] [varchar](12) NULL,
[LineLedger] [int] NOT NULL,
[PageLedger] [int] NOT NULL,
CONSTRAINT [PK_ClientSavingsDTL] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [ID]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [RefNo]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [CSHDRKey]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [TransNo]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [Amount]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [Type]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [TransType]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [Teller]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ('') FOR [Workstation]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PrnSlipYN]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PrnPassbookYN]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PrnLedgerYN]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [LineLedger]
GO

ALTER TABLE [dbo].[ClientSavingsDTL] ADD DEFAULT ((0)) FOR [PageLedger]
GO

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-04-26 : 14:03:41

Proper DDL for something like this would just include needed fields and also it is VERY IMPORTANT TO INCLUDE SAMPLE DATA, and your desired end results
ie.

Create table #ClientSavingsDTL (CSHDRKey int, id int,type as varchar(20),amount)
insert into #ClientSavingsDTL
select 1,10,'D',20
union all
select 2,30,'D',40
union all
select 3,40,'D',25
union all
select 140,10,'C',20
union all
select 170,30,'C',40
union all
select 180,40,'C',25

Then post what you want your results to be in this scenerio.

create table #Results(CSHDRKey int, id int,type as varchar(20),amount money,Debit money, Credit money, EndBal money, ADB money)
--WHAT DO YOU ULTIMATELY WANT YOUR RESULTS TO BE???

Please fill out/modify the tables/sample data so we can easily see exactly what your results should be, and then we can better assist.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources