Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert VBA code to stored procedure

Status
Not open for further replies.
hermanlaksko

hermanlaksko

Programmer
Aug 26, 2001
944
DK
Hi all

I have the below code that I need to "translate" to a stored procedure. Can anyone help?

The code:
Function FindIncl(Re As ADODB.Recordset, DisSet As ADODB.Recordset, RepSet As ADODB.Recordset, EcDate)
On Error GoTo Fejl
Dim CurIncl As Boolean, SumDis, SumRep, Antal As Integer, Recs As Integer, RecStr, Test As Boolean, CurRecNr, Bel, B
Dim Dis As New ADODB.Recordset, Rep As New ADODB.Recordset, ReNew As New ADODB.Recordset, ReDeal As New ADODB.Recordset, XX
Erase OpenDeals
Dis.Open Trim(Left(DisSet.Source, InStr(1, DisSet.Source, "Order by") - 1)) & " Where DisbursementDate<'" & Format(EcDate, "MM-DD-YYYY") & "' ORDER BY TransactionID", CurrentProject.Connection, 0, adLockBatchOptimistic
Rep.Open Trim(Left(RepSet.Source, InStr(1, RepSet.Source, "Order by") - 1)) & " Where RepaymentDate<'" & Format(EcDate, "MM-DD-YYYY") & "' ORDER BY TransactionID", CurrentProject.Connection, 0, 4
ReNew.Open Trim(Left(RepSet.Source, InStr(1, RepSet.Source, "Order by") - 1)) & " Where RepaymentDate>'" & Format(EcDate, "MM-DD-YYYY") & "' ORDER BY TransactionID", CurrentProject.Connection, 0, 4
Re.MoveFirst

Recs = Re.RecordCount
Do While Not Re.EOF
SumDis = 0: SumRep = 0: CurIncl = False
Dis.MoveFirst
If Dis.AbsolutePage > 0 Then Dis.Find "TransactionID='" & Re!TransactionID & "'"
If Dis.AbsolutePage > 0 Then
Do While Dis!TransactionID = Re!TransactionID
If Not IsNull(Dis!Amount) Then SumDis = SumDis + Dis!Amount
Dis.MoveNext
If Dis.AbsolutePage < 0 Then Exit Do
Loop
End If
Rep.MoveFirst
If Rep.AbsolutePage > 0 Then Rep.Find "TransactionID='" & Re!TransactionID & "'"
If Rep.AbsolutePage > 0 Then
Do While Rep!TransactionID = Re!TransactionID
If Not IsNull(Rep!Amount) Then
If Not Rep!Delay Then SumRep = SumRep + Rep!Amount
End If
Rep.MoveNext
If Rep.AbsolutePage < 0 Then Exit Do
Loop
End If
'Chk A
If (SumDis - SumRep) > 100 Then CurIncl = True
'Chk B
If Re!TotalCreditAmount - SumDis > 1 And Re!CommitmentDate < EcDate And Re!CommitmentTerminationDate > EcDate Then CurIncl = True
'Chk C - Chk for data>date
SumRep = 0
ReNew.MoveFirst
If Dis.AbsolutePage > 0 Then ReNew.Find "TransactionID='" & Re!TransactionID & "'"
If ReNew.AbsolutePage > 0 Then
Do While ReNew!TransactionID = Re!TransactionID
If Not IsNull(ReNew!Amount) Then
If Not ReNew!Delay Then SumRep = SumRep + ReNew!Amount
End If
ReNew.MoveNext
If ReNew.AbsolutePage < 0 Then Exit Do
Loop
End If
If SumDis - SumRep < 0 Then CurIncl = True
If CurIncl Then
If CurRecNr <> Re!TransactionID Then
Antal = Antal + 1
OpenDeals(Antal) = Re!TransactionID
OpenDis(Antal) = SumDis
OpenRep(Antal) = SumRep
CurRecNr = Re!TransactionID
End If
End If
Re.MoveNext
SysCmd acSysCmdSetStatus, "Selection of data for report " & Re.AbsolutePosition & " of " & Recs
Loop
End Function

The recordsets are made up of:
Recordset DisSet
1 TransactionID nvarchar 100 1
0 DisbursementID nvarchar 100 1
0 DisbursementDate datetime 8 1
0 DisbursementAmount money 8 1

Recordset Re
0 Fees float 8 1
0 RID uniqueidentifier 16 1
0 BorrowerYesNo int 4 1
0 FinalMaturity smalldatetime 4 1
0 DealsReferenceNumber nvarchar 20 1
0 TotalCreditAmount real 4 1
0 CommitmentDate smalldatetime 4 1
0 CommitmentTerminationDate smalldatetime 4 1
0 BID tinyint 1 1
0 TRUID tinyint 1 1
0 ClientsName nvarchar 50 1
0 ClientsReferenceNumber nvarchar 20 1
0 [Risk%] float 8 1
0 BookingUnitID tinyint 1 1
0 OffBalance bit 1 1
0 BorrowerName nvarchar 50 1
0 Rate real 4 1
0 TVal nvarchar 3 1
0 RepaymentAbility nvarchar 3 1
0 CollateralCoverage tinyint 1 1
0 RatingType tinyint 1 1
0 RatingOther nvarchar 10 1
0 CRUDetail nvarchar 10 1
0 Client_PropertyID tinyint 1 1
0 CRUID tinyint 1 1
0 CRUName nvarchar 50 1
0 CRULevel3 nvarchar 50 1
0 CRUGroup nvarchar 50 1
0 BusinessArea nvarchar 20 1
0 Category nvarchar 1 1
0 TCountry nvarchar 3 1
0 BookingUnit nvarchar 50 1
0 CID smallint 2 1
0 SIC nvarchar 5 1
0 IndustriesName nvarchar 200 1
0 UnitsName nvarchar 50 1
0 BusinessUnit nvarchar 10 1
0 MaxOfRepaymentDate smalldatetime 4 1
0 ClientName nvarchar 50 1
0 Initials nvarchar 3 1
0 ExporterCRULevel2 nvarchar 50 1
0 ExporterCRULevel3 nvarchar 50 1
0 Margin float 8 1
0 CommitmentFee float 8 1
0 TID uniqueidentifier 16 1
0 Product nvarchar 50 1
0 Product3digit nvarchar 3 1
0 TransCRU tinyint 1 1
0 TransactionID nvarchar 50 1
1 Incl bit 1 1

Recordset RePay
0 TransactionID nvarchar 100 1
0 Delay bit 1 1
0 RepaymentID nvarchar 100 1
0 RepaymentDate datetime 8 1
1 RepaymentAmount money 8 1


Herman
Say no to macros
 
Sort by date Sort by votes
Not much I am afraid:

CREATE PROCEDURE dbo.FindIncl @EcDate DateTime AS
DECLARE @CurIncl AS Bit
DECLARE @SumDis AS Money
DECLARE @SumRep AS MONEY
DECLARE @Antal AS Integer
DECLARE @Recs AS Integer
DECLARE @RecStr AS NVarChar(50)
DECLARE @CurRecNr AS NVarChar(50)
DECLARE @TID AS NVarChar(50)
DECLARE @BYN AS Bit

DECLARE DisR CURSOR FOR SELECT TransactionID, DisbursementDate, DisbursementAmount AS Amount FROM EcDis WHERE DisbursementDate<= @EcDate ORDER BY TransactionID
DECLARE RepR CURSOR FOR SELECT TransactionID, RepaymentDate, RepaymentAmount AS Amount, Delay FROM EcRepay WHERE RepaymentDate<= @EcDate ORDER BY TransactionID

DECLARE ReNew CURSOR FOR Select EcRep.* From EcRep ORDER BY TransactionID, BorrowerYesNo DESC, ClientsName, [Risk%] DESC
OPEN ReNew
FETCH NEXT FROM ReNew INTO
@TID , @BYN
WHILE @@fetch_status =
BEGIN

FETCH Next From ReNew Into @TID
end

Herman
Say no to macros
 
Upvote 0 Downvote
The first thing I'd say is to get out of the habit of using a cursor. They are generally slow and you don't often need them unless you are doing anything more geared towards admin. Have a look at a set based approach instead which is what you will need 99% of the time.

The second would be to look at the database structure. Forget what the existing function does for now, trying to convert it line by line will probably just make it over complicated anyway. Look at the main tables you need, what information do they contain? What other tables will you need to join to? What values do you need to perform calculations on? What facts are you trying to report?

If you can look at it from this angle, and if you struggle try to explain it to us, then you're more likely to come up with an efficient method.


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Upvote 0 Downvote
Mark is right. Do not use a cursor. And he is right. Start thinking terms of the datbase tables and what you want from them. Describe to use the table structure. Give sample data and sample results and we will be more able to hellp you.

I'll give you hint, the if staments will be Case statements in a SQL Server query.



"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Ok so I understand do not use cursors as they are slow, but could I get en example of what to use if not cursors

Herman
Say no to macros
 
Upvote 0 Downvote
I am trying to loop thru 3 tables
1. Main tabel
2. Sub tabel holding values that, when added up, determining the value of a field in the main table
3. 2nd sub table - same issue.

Herman
Say no to macros
 
Upvote 0 Downvote
Upvote 0 Downvote
Upvote 0 Downvote
When you are thinking of SQl server forget about looping, never loop. Looping shouldn't even exist an option to you until you learn to use joins and set-based logic instead. A lot of programmers who come from other envirnoments are used to looping and that is where they go first.

Start with what we asked you to do, give us table structure , sample data and sample results.

"NOTHING is more important in a database than integrity." ESquared
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
1K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
2K
mikrom
mikrom
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
2K
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
791
Volvere
V
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
791
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top