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

>> 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 ??;

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.

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.

And good SQL programmers seldom use it. That will take awhile for this guy to learn that, I fear.

The monolithic mindset versus tiered architecture

Amen!

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.

Your foolishness is on display here:
Phone Number formatting hell