SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex stored procedures with variables and branching


Complex stored procedures with variables and branching

Author
Message
joe-584802
joe-584802
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 131
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

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)

Group: General Forum Members
Points: 347424 Visits: 34119
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207708 Visits: 24220
Grant Fritchey - Thursday, February 8, 2018 4:50 AM
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.

I've found this to be the case more often than not.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
joe-584802
joe-584802
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 131
Grant Fritchey - Thursday, February 8, 2018 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.

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

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)SSC Guru (347K reputation)

Group: General Forum Members
Points: 347424 Visits: 34119
joe-584802 - Thursday, February 8, 2018 6:30 AM

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


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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search