Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2013
    Posts
    1

    Question Stored Proc equivalent for VBA Select Case

    I'd like to turn my VBA into a stored proc. I'm new to TSql and appreciate any assistance!

    Public Function GetDepreciationFactor(TypeCode As Integer, ValueType As Integer, ItemAge As Integer) As Double
    Dim DepreciationRate As Double
    Dim sql As String
    Dim rs As DAO.Recordset

    On Error GoTo Errors
    'Stop
    If TypeCode >= 20 And TypeCode <= 23 Then 'tugboats and barges
    MsgBox "This depreciation schedule must be handled manually. E.g., tugboats and barges."
    Exit Function
    End If

    sql = "SELECT * From tlkpValuationSchedule WHERE ValuationCode=" & TypeCode & " ORDER BY ItemAge"
    Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

    Select Case ValueType
    Case 1
    Do
    DepreciationRate = rs!DepreciationPercent
    If ItemAge = rs!ItemAge Then Exit Do
    rs.MoveNext
    Loop Until rs.EOF
    ' Case 2
    Case 2, 3 'Flat value & Owner Value; no depreciation
    DepreciationRate = 1
    End Select

    GetDepreciationFactor = DepreciationRate

  2. #2
    Join Date
    Jan 2013
    Posts
    310

    “Before you can drink new tea, you must empty the old tea from your cup.” Soto Zen pr

    >> I'd like to turn my VBA into a stored proc. I'm new to T-SQL and appreciate any assistance! <<

    “Before you can drink new tea, you must empty the old tea from your cup.” Soto Zen proverb.

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    The first problem is that your data model is a mess. There is no such thing as a “type_code”; a data element is a “<something>_type” or a “<something>_code”, not an absurd string of what we call attribute properties. Think of “blood_type” and “postal_code” in a valid data model. We also like to compute age because it changes.

    Dynamic SQL is an admission of failure. It tells the world that you have so little knowledge and control, you or a random stranger has to figure out what needs to be done at the last possible second. And that you do not care about SQL injection.

    SQL is a database language. PERIOD. It does not display messages about “tugboats and barges”; that is the presentation layer in a tiered architecture. We use DECIMAL() over FLOAT, REAL or DOUBLE PRECISION. There are legal reasons as well as mathematical ones. The idea is strong functional cohesion; the procedure does one and only one thing.

    We have no specs, but this sounds like a simple table look-up problem. Wild guess at a skeleton:

    CREATE PROCEDURE Get_Depreciation_Rate
    (@in_item_depreciation_type ?? )

    SELECT depreciation_rate,
    CAST (CURRENT_TIMESTAMP AS DATE) AS current_date, ..
    FROM Depreciation_Schedule
    WHERE @in_depreciation_type = ??
    AND <compute age from CURRENT_TIMESTAMP> = ??
    AND ??;

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    NSSmith, its not clear what you want to do here.
    TSQL does have a CASE statement, which can be used to return different results as well as control process flow.
    TSQL also has IF/THEN logic.
    TSQL can't effectively call your MsgBox routine, of course, that it could return a message that you could then display however you wish.

    Please explain the business requirements, rather than posting VBA code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2013
    Posts
    310
    T-SQL does have a CASE statement, which can be used to return different results as well as control process flow.
    No, we have a CASE expression in SQL. Expressions return a scalar value of a known data type by definition. It has nothing to do with control flow because declarative languages have no execution flow. This is a big part of his problem his mindset is still stuck in VBA and W's procedural coding.

    T-SQL also has IF/THEN logic.
    And good SQL programmers seldom use it. That will take awhile for this guy to learn that, I fear.

    T-SQL can't effectively call your Mailbox routine, of course, that it could return a message that you could then display however you wish.
    The monolithic mindset versus tiered architecture

    Please explain the business requirements, rather than posting VB code.
    Amen!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    Quote Originally Posted by Celko View Post
    T-SQL also has IF/THEN logic.
    And good SQL programmers seldom use it. That will take awhile for this guy to learn that, I fear.
    I'm an excellent SQL programmer, and find it necessary to use IF/THEN logic frequently.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2013
    Posts
    310
    I'm an excellent SQL programmer, and find it necessary to use IF/THEN logic frequently.
    No, you are not even a good SQL programmer yet. I am being objective, not mean.

    Go look at the code you posted for removing non-digit characters from phone numbers in another thread. You wrote a 1970's BASIC function in T-SQL, not good SQL. You did not follow ISO-11179 rules for names, your function will have to be invoked row-by-row (ever hear the RDBMS slang RBAR? Look it up; Jeff Modem coined it), you scan the input string character-by-character, left to right. This is how a punch card or mag tape works. You did nothing in a declarative or set-oriented way!

    I replaced your code with one update statement that will run orders of magnitude faster and the code is portable. You literally posted a version of a bad example I use in my SQL classes.

    You had local variables to add the overhead of disk access. But more than that, it means you do not understand the declarative/functional model of programming. Please read An Introduction to Functional Programming by Greg Michaelson, if it will help. Or talk to a LISP, APL or F# programmer.

    Since SQL got the CASE expression in SQL-92, I have not used IF-THEN logic. Primitive recursive function theory guarantees I can do this! I do not write UDFs, or TRIGGERs either. My rule of thumb is no more than five triggers in your whole career, and most of those should be INSTEAD OF triggers.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    Quote Originally Posted by Celko View Post
    No, you are not even a good SQL programmer yet. I am being objective, not mean.
    Your foolishness is on display here:
    http://www.dbforums.com/microsoft-sq...ml#post6582594
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •