If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Stored Proc equivalent for VBA Select Case

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 267
“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 ??;
Reply With Quote
  #3 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,521
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 267
Quote:
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.

Quote:
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.

Quote:
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

Quote:
Please explain the business requirements, rather than posting VB code.
Amen!
Reply With Quote
  #5 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,521
Quote:
Originally Posted by Celko View Post
Quote:
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 267
Quote:
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.
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,521
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:
Phone Number formatting hell
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Tags
stored proc, tsql, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On