February 7, 2018 at 12:58 pm
Hi Guys,
I'm pretty comfortable using T-SQL for most simple CRUD situations. I now want to move onto a more complex stored procedure to replace a VB function that takes several input parameters which I will call A, B, C, D, E and F and then performs a number of processes step by step to ultimately produce 2 output values known as a 'Wholesale Charge' and a 'Default Charge'
For example the first process is to get a list of distinct values from a table where x = input parameter A.
The second process is to loop through these list of distinct values which in turn look up values from another table.
The distinct values looked up from the second process will determine how I get my 2 output values.
If just a single record is returned then it will be a case of selecting the 2 values and passing them into 2 output variables.
If on the next loop a range of records is found then I may have to find my 2 values where input parameter B is between a lower field value and an upper field value within the list of records selected. These will be added to my 2 output variables
The third loop may give me a single record that requires me to multiply my selected values with parameter C and divided by parameter D and then add the result to my 2 output variables.
When all the looping is completed I then need to pass the values accumulated in my output variables as outputs to my stored procedure.
Apologies if I'm being a bit loose with my description above but what I'm after is some examples for this type of structure so I can then apply my data to it and keep the business logic separate from my front-end application
The function I've written in VB is as follows so you can get the gist of how it could work in T-SQL.
Thank you in advance for any help or pointers you can give me.
Joe
Public Function GetQuoteSummary(intRow As Long, sngMeterSize As Single, sngConsumption As Single, sngWSRateableValue As Single, sngSSRateableValue As Single, sngWSPipeSize As Single, sngSSPipeSize As Single, sngSewerMeterSize As Single, sngReturnToSewer As Single, sngAssessedChargeableMeter As Single, sngAssessedVolumetricRate As Single, sngSurfaceArea As Single, sngAreaDrained As Single, sngMPWMaxDailyDemand As Single, sngMNPWMaxDailyDemand As Single)
On Error Resume Next
Dim rs1 As ADODB.Recordset, strSQL As String, sngTotWholesaleCharge As Single, sngTotDefaultCharge As Single, sngWholesaleCharge As Single, sngDefaultCharge As Single, sngValueToApply As Single, sngRVThreshold As Single, sngMinCharge1 As Single, sngMinCharge2 As Single, sngMaxCharge1 As Single, sngMaxCharge2 As Single
Dim strChargeElement As String
Dim strMissingTariffs As String
Set rs1 = New ADODB.Recordset
For i = 1 To 9
If Len(TariffCodes(i)) > 0 Then ' we have a value
strSQL = "SELECT [Charge Element Billing Field] "
strSQL = strSQL & "FROM dbo.tblWholesaleTariff "
strSQL = strSQL & "GROUP BY [Tariff Code], [Charge Element Billing Field] "
strSQL = strSQL & "HAVING ([Tariff Code] = '" & TariffCodes(i) & "')"
Set rs1 = oConn.Execute(strSQL)
Do Until rs1.EOF
strChargeElement = Trim$(rs1![Charge Element Billing Field])
'Debug.Print TariffCodes(i), strChargeElement
sngWholesaleCharge = 0
sngDefaultCharge = 0
Select Case strChargeElement
Case "D7102", "D7152", "D7201", "D7251", "D7302", "D7351", "D7401", "D7454", "D7504"
Call CalcRule1(TariffCodes(i), strChargeElement, sngWholesaleCharge, sngDefaultCharge)
Case "D7101", "D7151", "D7264", "D7301", "D7352", "D7414", "D7451", "D7501", "D7158", "D7108", "D7202"
Select Case strChargeElement
Case "D7264"
sngValueToApply = sngWSPipeSize
Case "D7414"
sngValueToApply = sngSSPipeSize
Case "D7301"
sngValueToApply = sngSewerMeterSize
Case "D7202", "D7352"
sngValueToApply = sngAssessedChargeableMeter
Case "D7451"
sngValueToApply = sngAreaDrained
Case "D7501"
sngValueToApply = sngSurfaceArea
Case "D7158"
sngValueToApply = sngMPWMaxDailyDemand
Case "D7108"
sngValueToApply = sngMPWMaxDailyDemand
Case "D7158"
sngValueToApply = sngMNPWMaxDailyDemand
Case Else
sngValueToApply = sngMeterSize
End Select
Call CalcRule2(sngValueToApply, TariffCodes(i), strChargeElement, sngWholesaleCharge, sngDefaultCharge)
Case "D7402", "D7455", "D7505", "D7252"
Select Case strChargeElement
Case "D7252"
sngValueToApply = IIf(sngWSRateableValue = 0, sngSSRateableValue, sngWSRateableValue)
Case "D7402", "D7505", "D7455"
sngValueToApply = IIf(sngSSRateableValue = 0, sngWSRateableValue, sngSSRateableValue)
Case Else
sngValueToApply = sngConsumption
End Select
Call CalcRule3(sngValueToApply, TariffCodes(i), strChargeElement, sngWholesaleCharge, sngDefaultCharge)
Select Case strChargeElement 'possibility of the return to sewer is less than 100%
Case "D7252", "D7402"
sngMinCharge1 = 0
sngMaxCharge1 = 0
sngMinCharge2 = 0
sngMaxCharge2 = 0
sngMinCharge1 = GetMaxMinCharge("Wholesale Charge", TariffCodes(i), Switch(strChargeElement = "D7252", "D7255", strChargeElement = "D7402", "D7405"))
sngMinCharge2 = GetMaxMinCharge("Default Charge", TariffCodes(i), Switch(strChargeElement = "D7252", "D7255", strChargeElement = "D7402", "D7405"))
sngMaxCharge1 = GetMaxMinCharge("Wholesale Charge", TariffCodes(i), Switch(strChargeElement = "D7252", "D7254", strChargeElement = "D7402", "D7404"))
sngMaxCharge2 = GetMaxMinCharge("Default Charge", TariffCodes(i), Switch(strChargeElement = "D7252", "D7254", strChargeElement = "D7402", "D7404"))
If sngMinCharge1 = 0 And sngMaxCharge1 = 0 Then
GoTo skip
ElseIf sngMaxCharge1 >= 0 And sngWholesaleCharge > sngMaxCharge1 Then
sngWholesaleCharge = sngMaxCharge1
sngDefaultCharge = sngMaxCharge2
ElseIf sngMinCharge1 >= 0 And sngWholesaleCharge < sngMinCharge1 Then
sngWholesaleCharge = sngMinCharge1
sngDefaultCharge = sngMinCharge2
End If
sngRVThreshold = 0
sngRVThreshold = GetRVThreshold(TariffCodes(i), Switch(strChargeElement = "D7252", "D7253", strChargeElement = "D7402", "D7403", strChargeElement = "D7505", "D7506"))
If sngRVThreshold < sngValueToApply Then
sngWholesaleCharge = 0
sngDefaultCharge = 0
End If
End Select
Case "D7103", "D7153", "D7203", "D7303", "D7353", "D7460", "D7510"
Select Case strChargeElement
Case "D7353"
sngValueToApply = sngAssessedVolumetricRate
Case "D7303"
sngValueToApply = sngConsumption * (IIf(sngReturnToSewer > 0, sngReturnToSewer / 100, 1))
Case Else
sngValueToApply = sngConsumption
End Select
Call CalcRule4(sngValueToApply, TariffCodes(i), strChargeElement, sngWholesaleCharge, sngDefaultCharge)
Case "D7108", "D7158", "D7253", "D7254", "D7255", "D7403", "D7404", "D7405", "D7456", "D7506" 'these are used as separate lookups
'do nothing
Case Else
strMissingTariffs = strMissingTariffs & "'" & strChargeElement & "';"
End Select
skip:
sngTotWholesaleCharge = sngTotWholesaleCharge + Round(sngWholesaleCharge, 2)
sngTotDefaultCharge = sngTotDefaultCharge + Round(sngDefaultCharge, 2)
rs1.MoveNext
Loop
rs1.Close
End If
Next
Set rs1 = Nothing
If strMissingTariffs <> "" Then
MsgBox "The following Tariff Code(s) was/were not accounted for in calculating the quotation!" & vbCrLf & vbCrLf & Left(strMissingTariffs, Len(strMissingTariffs) - 1), vbExclamation, "Incorrect Calculation"
End If
iGrid10.cellvalue(intRow, "WholesaleCharge") = Round(sngTotWholesaleCharge, 2)
iGrid10.cellvalue(intRow, "DefaultCharge") = Round(sngTotDefaultCharge, 2)
PopulateQuoteSummary
End Function
February 8, 2018 at 4:50 am
Writing out a full fledged T-SQL for all that would be a full-time position and I ain't getting paid. Ha!
However, I'll offer a few pieces of advice.
First, and most important, get loops and whiles and, as much as possible cases out of your head. Set-based operations is how you need to think in T-SQL. Yeah, you can do pre-loading on sets (meaning put stuff into temp tables or table variables) and then work with those sets, but get away from "for each row do this" thinking. It'll kill you in T-SQL. It's just not good at that. What it is good at is sets.
Next, branches in T-SQL code basically mean, welcome to a new procedure. You don't want branched code inside of a single procedure. It has to do with how T-SQL gets compiled. It compiles the entire batch, all branches, all code, as a set. So compiling while tables are empty or for branch values that could result in one row or one million rows will result in plans that are pretty horrible. When you have a break in logic, we're going down PathB instead of PathA, then that's a new procedure. In fact, create a wrapper procedure that determines paths and then a procedure for each path. Yeah, more work, but you'll thank me after it's done.
Avoid ad hoc code. If you find that you have to build every single query through a series of coded statements, your requirements are off, your database design is off, or the coding is off (ad hoc reporting being the exception here).
Finally, eat the elephant. Go at this one step at a time. Figure out a set-based mechanism to gather your initial data set. Then, determine if you can modify that set for the next step, in the first step. If not, are you looking at "well, for each row..."? If so, stop, reevaluate. There's nothing wrong with, initially, putting it all into temp tables and then putting all that together in a few steps. It doesn't have to be a single, magic query. In fact, sometimes, even if you can do something in one giant uber-statement, it may not perform as well as two or three smaller chunks of code.
Just test the code, the data, and, most importantly, your assumptions as you go.
If you do get to specific steps that are bugging you, post 'em here and you'll get help. Heck, you may get one of our crazed members to take this as a challenge and write the whole thing based on the p-code you provided.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2018 at 5:07 am
Grant Fritchey - Thursday, February 8, 2018 4:50 AMIn fact, sometimes, even if you can do something in one giant uber-statement, it may not perform as well as two or three smaller chunks of code.
I've found this to be the case more often than not.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 8, 2018 at 6:30 am
Grant Fritchey - Thursday, February 8, 2018 4:50 AMWriting out a full fledged T-SQL for all that would be a full-time position and I ain't getting paid. Ha!However, I'll offer a few pieces of advice.
First, and most important, get loops and whiles and, as much as possible cases out of your head. Set-based operations is how you need to think in T-SQL. Yeah, you can do pre-loading on sets (meaning put stuff into temp tables or table variables) and then work with those sets, but get away from "for each row do this" thinking. It'll kill you in T-SQL. It's just not good at that. What it is good at is sets.
Next, branches in T-SQL code basically mean, welcome to a new procedure. You don't want branched code inside of a single procedure. It has to do with how T-SQL gets compiled. It compiles the entire batch, all branches, all code, as a set. So compiling while tables are empty or for branch values that could result in one row or one million rows will result in plans that are pretty horrible. When you have a break in logic, we're going down PathB instead of PathA, then that's a new procedure. In fact, create a wrapper procedure that determines paths and then a procedure for each path. Yeah, more work, but you'll thank me after it's done.
Avoid ad hoc code. If you find that you have to build every single query through a series of coded statements, your requirements are off, your database design is off, or the coding is off (ad hoc reporting being the exception here).
Finally, eat the elephant. Go at this one step at a time. Figure out a set-based mechanism to gather your initial data set. Then, determine if you can modify that set for the next step, in the first step. If not, are you looking at "well, for each row..."? If so, stop, reevaluate. There's nothing wrong with, initially, putting it all into temp tables and then putting all that together in a few steps. It doesn't have to be a single, magic query. In fact, sometimes, even if you can do something in one giant uber-statement, it may not perform as well as two or three smaller chunks of code.
Just test the code, the data, and, most importantly, your assumptions as you go.
If you do get to specific steps that are bugging you, post 'em here and you'll get help. Heck, you may get one of our crazed members to take this as a challenge and write the whole thing based on the p-code you provided.
Thank you for taking the time in giving your advice! Where can I find useful tutorials on digging deeper with T-SQL and maybe linking procedures together?
Thanks again.
Joe
February 8, 2018 at 6:45 am
joe-584802 - Thursday, February 8, 2018 6:30 AMThank you for taking the time in giving your advice! Where can I find useful tutorials on digging deeper with T-SQL and maybe linking procedures together?
Thanks again.Joe
Over on the upper left of the screen is a link that says "Stairways" click on that. There are a bunch of excellent tutorials that'll help you out. After that, I always recommend that everyone who has to deal with T-SQL have a copy of Itzik Ben-Gan's book, T-SQL Querying. Beyond that, we'd have to get into specifics. There are tons of articles here on SQL Server Central and also over at Simple-Talk that can help you out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply