Converting VBA to SQL stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting VBA to SQL stored procedure

Author

Topic

vavs
Starting Member

24 Posts

Posted - 2010-08-27 : 11:00:07

I have an ERP system running on UNIX. Twice a day, I move data via FTP to a SQL database on my Windows Server. From this point I have created a plethora of views that I use in various Access and Excel functions. I have a function in Access that is causing me fits. It takes forever to run. The only saving grace is that I only run it once a week. I would like to move the program from Access to a SQL stored procedure to speed up the processing. The tricky part for me initially was handling of one field called Gauge. In the original program the field is a text field. What I did in the Access program is to take the text field and determine what its numeric equivalents were. I needed to be able to find inside, outside and average. I did this by looking for a character string and then moving left or right to get the inside and outside dimensions. Once I had this, I used the CAST function to make it a number. I made the average inside plus outside / 2. There were about 15 different scenarios to check for to get to the inside and outside number. In one instance if the field contained GA, I did a lookup in another table that inserted the inside and outside values.

What I am hoping for is someone who can walk me through my first stored procedure based on the VBA function I have. I am more than happy to provide anyone the script to review.

Thanks in advance for any help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-27 : 11:10:16

show the script

vavs
Starting Member

24 Posts

Posted - 2010-08-27 : 11:50:18

Here is the code


Public Sub InvUpdate()

'On Error GoTo ErrHandler
Dim MyDb As DAO.Database
Dim MyRs As DAO.Recordset
Dim counter
Dim DecIn As Double, DecOut As Double, DecAvg As Double
Dim GF


Set MyDb = CurrentDb

Set MyRs = MyDb.OpenRecordset("tblTMIINV", dbOpenDynaset)
Set MyRs2 = MyDb.OpenRecordset("tblGauge", dbOpenDynaset)
With MyRs
    If .BOF And .EOF Then
    'GoTo ExitTransform
    End If
.MoveFirst

Do While Not .EOF
    DecIn = 999
    DecOut = 999
    GF = 999
    
    If InStr(.Fields("Gauge"), "MIN") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "MIN") - 2)
        DecOut = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "MIN") - 2)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
      
    Else
    
    If InStr(.Fields("Gauge"), "NOM") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "NOM") - 2)
        DecOut = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "NOM") - 2)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    
    Else
    
    If InStr(.Fields("Gauge"), "ACT") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "ACT") - 2)
        DecOut = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "ACT") - 2)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "1/4") Then
        DecIn = 0.25
        DecOut = 0.25
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "5/16") Then
        DecIn = 0.375
        DecOut = 0.375
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "3/8") Then
        DecIn = 0.3125
        DecOut = 0.3125
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "/") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "/") - 1)
        DecOut = Mid(.Fields("Gauge"), InStr(.Fields("Gauge"), "/") + 1, 99)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "-") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "-") - 1)
        DecOut = Mid(.Fields("Gauge"), InStr(.Fields("Gauge"), "-") + 1, 99)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "1/4") Then
        DecIn = .Fields("Gauge")
        DecOut = .Fields("Gauge")
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
    If InStr(.Fields("Gauge"), "3/8") Then
        DecIn = 0.3125
        DecOut = 0.3125
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
    If InStr(.Fields("Gauge"), "5/16") Then
        DecIn = 0.375
        DecOut = 0.375
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
    If InStr(.Fields("Gauge"), "GA") Then

        DecIn = DLookup("Low", "tblGauge", "Gauge = '" & .Fields("Gauge") & "' AND Grade = '" & .Fields("Type") & "'")
        DecOut = DLookup("High", "tblGauge", "Gauge = '" & .Fields("Gauge") & "' AND Grade = '" & .Fields("Type") & "'")
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
        DecIn = .Fields("Gauge")
        DecOut = .Fields("Gauge")
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
    
    
    
    .Edit
    .Fields("DecIn") = DecIn
    .Fields("DecOut") = DecOut
    .Fields("DecAvg") = DecAvg
    .Fields("GaugeFinal") = GF
    .Update
    .MoveNext
Loop
End With

'ExitTransform
    'Set MyRs = Nothing
    'Set MyDb = Nothing
    'Exit Sub

'ErrHandler
    'Resume Next
    
        
    
End Sub

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-27 : 11:51:30

OK, not that I've read through this...but this has GOT to be the best question EVER asked on SQL team

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-08-27 : 11:58:53

And I'll throw a minature lifebelt to you...Investigate the CASE construct.
And if you can - post the tblTMIINV and tblGauge structures (and sample data)

nheidorn
Starting Member

28 Posts

Posted - 2010-08-27 : 12:12:12

The real question I have is whether a 1-to-1 rewrite is prudent. You could take this opportunity to come at the problem a completely different way. For example, you could build a temporary table of the various lookup values and then update them from tblGauge in a single UPDATE statement.

That said, here are a few replacements that may come in handy:

InStr becomes CHARINDEX
Dlookup becomes SET @Variable = (SELECT FieldName FROM TableName WHERE This=That;)
Left stays LEFT

If STAYS IF but the else works a little differently:
[CODE]IF This = That BEGIN
Do Stuff
END
ELSE BEGIN
Do Something Else
END[/CODE]
And, as Andrew said, CASE may save you from nested IF hell. Good luck!

vavs
Starting Member

24 Posts

Posted - 2010-08-27 : 14:11:43

Here is the code


Public Sub InvUpdate()

'On Error GoTo ErrHandler
Dim MyDb As DAO.Database
Dim MyRs As DAO.Recordset
Dim counter
Dim DecIn As Double, DecOut As Double, DecAvg As Double
Dim GF


Set MyDb = CurrentDb

Set MyRs = MyDb.OpenRecordset("tblTMIINV", dbOpenDynaset)
Set MyRs2 = MyDb.OpenRecordset("tblGauge", dbOpenDynaset)
With MyRs
    If .BOF And .EOF Then
    'GoTo ExitTransform
    End If
.MoveFirst

Do While Not .EOF
    DecIn = 999
    DecOut = 999
    GF = 999
    
    If InStr(.Fields("Gauge"), "MIN") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "MIN") - 2)
        DecOut = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "MIN") - 2)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
      
    Else
    
    If InStr(.Fields("Gauge"), "NOM") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "NOM") - 2)
        DecOut = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "NOM") - 2)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    
    Else
    
    If InStr(.Fields("Gauge"), "ACT") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "ACT") - 2)
        DecOut = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "ACT") - 2)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "1/4") Then
        DecIn = 0.25
        DecOut = 0.25
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "5/16") Then
        DecIn = 0.375
        DecOut = 0.375
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "3/8") Then
        DecIn = 0.3125
        DecOut = 0.3125
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "/") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "/") - 1)
        DecOut = Mid(.Fields("Gauge"), InStr(.Fields("Gauge"), "/") + 1, 99)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "-") Then
        DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "-") - 1)
        DecOut = Mid(.Fields("Gauge"), InStr(.Fields("Gauge"), "-") + 1, 99)
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
        
    Else
    
    If InStr(.Fields("Gauge"), "1/4") Then
        DecIn = .Fields("Gauge")
        DecOut = .Fields("Gauge")
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
    If InStr(.Fields("Gauge"), "3/8") Then
        DecIn = 0.3125
        DecOut = 0.3125
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
    If InStr(.Fields("Gauge"), "5/16") Then
        DecIn = 0.375
        DecOut = 0.375
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
    If InStr(.Fields("Gauge"), "GA") Then

        DecIn = DLookup("Low", "tblGauge", "Gauge = '" & .Fields("Gauge") & "' AND Grade = '" & .Fields("Type") & "'")
        DecOut = DLookup("High", "tblGauge", "Gauge = '" & .Fields("Gauge") & "' AND Grade = '" & .Fields("Type") & "'")
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    Else
    
        DecIn = .Fields("Gauge")
        DecOut = .Fields("Gauge")
        DecAvg = ((DecIn) + (DecOut)) / 2
        GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")
    
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
    
    
    
    .Edit
    .Fields("DecIn") = DecIn
    .Fields("DecOut") = DecOut
    .Fields("DecAvg") = DecAvg
    .Fields("GaugeFinal") = GF
    .Update
    .MoveNext
Loop
End With

'ExitTransform
    'Set MyRs = Nothing
    'Set MyDb = Nothing
    'Exit Sub

'ErrHandler
    'Resume Next
    
        
    
End Sub

vavs
Starting Member

24 Posts

Posted - 2010-08-27 : 14:34:44

Here are the table structures:

tblGauge

Grade Text
Low Number
High Number
Gauge Text

tblTMIINV

TAG# Text
GRADE Text
Gauge Text
Width Number
Weight Number
Tons Number
Status Number
Cost-Cwt Number
GaugeType Text
Type Text
DecIn Number
DecOut Number
DecAvg Number
GaugeFinal Text
InvValue Currency

tblTMIINV exists in SQL and is linked to Access. tblGauge only exists in Access right now.

Andrew - I am a virtual and actual novice with SQL stored procedures. The case construct I am guessing operates like a case statement everywhere else. What I am struggling with is all of the symbology that seems to be associated here. Are tables preceeded with the @ symbol etc. If I get a few of the statements down I think I can get through the logic.

The stored procedure will also help me avoid having to create multiple temporary tables to convert data from text to number.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-08-30 : 07:40:30

ignore the SP route for the moment. that is cream on the cake. but SP's aren't any more complex anyway.

concentrate on getting the SQL code working first.
MSSQL tables are just named "nothing-special"...other than those with # (##) prefixed names - which are Temporary adhoc tables.
the "@" is used to indicate a variable - changable in value and only existing within the run of the SP.

sample SQL code with case constructs.

select
mycol1,
case
when mycol2 = valuea then valueb
when mycol2 * mycol3 = valuec then valued -- just to show that the "when" statements can be complex)
else valuex
end as 'mycolalias'
from mytable

will both tables be moving into SQL?

vavs
Starting Member

24 Posts

Posted - 2010-08-30 : 08:56:57

Andrew,

I have one table already in my SQL database. The gauge table can be added without issue. It already exists in Access. I am really trying to create a new table that has 5 new fields in it. These fields are either a result of parsing a text field, matching another table or doing a calculation.

In the originating table(tblTMIINV) I have two key fields, Grade and Gauge. I need to match Grade to a table that will convert Grade code into another code. For example HRPO would be matched in the table and return HR. There are about 60 source codes and 6 returned codes.

The second thing I am doing is taking the text field Gauge and looking for one of the conditions. If there is a "/" in the text field I need to take everything to the left of the "/" and put it in the field DecIn converting it to a 0.00 decimal. Then take everything to the right of the "/" and put it in field DecOut converting it to a 0.00 decimal. Then I need to average the two numbers and put it in DecAvg.

Finally I need to find the gauge name based on the Grade and DecAvg values.

Here is what the code looks like in Access:

If InStr(.Fields("Gauge"), "/") Then
DecIn = Left(.Fields("Gauge"), InStr(.Fields("Gauge"), "/") - 1)
DecOut = Mid(.Fields("Gauge"), InStr(.Fields("Gauge"), "/") + 1, 99)
DecAvg = ((DecIn) + (DecOut)) / 2
GF = DLookup("Gauge", "tblGradeFinal", "((Low <= " & DecAvg & " AND High >= " & DecAvg & ") AND Grade = '" & .Fields("Type") & "')")

Else

Now if I can convert this statement into the SQL equivalent I am half way there correct?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-30 : 10:46:09

This is a start. It assumes that DecAvg is an integer. I don't know what yhe DLOOKUP is doing, so you'll either have to explain it better or figure it out on your own. It looks like you'll be setting a value based on a select from table query.

Jim

DECLARE @Field varchar(20)
DECLARE @pos tinyint
DECLARE @DecIn int
DECLARE @DecOut int
DECLARE @DecAvg int

SET @field = '121464/31789'
SET @pos = charindex('/',@field)
SET @DecIn = SUBSTRING(@field,1,@pos-1)
SET @DecOut = SUBSTRING(@field,@pos+1,99)
SET @DecAvg = (@DecIn +@DecOut)/ 2

select @Field as Field,@Decin as DecIn,@DecOut as DecOut,@DecAvg as DecAvg

Everyday I learn something that somebody else already knew

vavs
Starting Member

24 Posts

Posted - 2010-08-30 : 15:16:23

Jim,

The dlookup is a query statement that finds the correct value for the Final Gauge field. It is saying Select Gauge from tblGradeFinal where (tblGradeFinal.Low >= DecAvg and tblGradeFinal.High <= DecAvg) and tblGradeFinal.Grade = Type.

It is finding a value based on the DecAvg and Type.

I understand the logic you were showing me. Can I do a case statement that says:
/*
Case charindex('/',@field)<>0

DECLARE @Field varchar(20)
DECLARE @pos tinyint
DECLARE @DecIn int
DECLARE @DecOut int
DECLARE @DecAvg int

SET @field = '121464/31789'
SET @pos = charindex('/',@field)
SET @DecIn = SUBSTRING(@field,1,@pos-1)
SET @DecOut = SUBSTRING(@field,@pos+1,99)
SET @DecAvg = (@DecIn +@DecOut)/ 2

update DecIn = @DecIn
update DecOut = @DecOut
update DecAvg = @DecAvg
update Type = tblGrades.Type where tbGrades.Grade=Grade
update GaugeFinal = dlookup logic
'select @Field as Field,@Decin as DecIn,@DecOut as DecOut,@DecAvg as DecAvg

Else

Case 2
*/?

Also the SET @field = statement has me worried. I have a table with about 12000 records. How do I SET the variable to be whatever is in that field in each of the rows? Finally, what I would like to do is to update the table to include the five new fields based on the case statement. From there I can link it into Excel or Access as needed.

vavs
Starting Member

24 Posts

Posted - 2010-08-30 : 17:35:23

I tried to write the stored procedure but I am having trouble with the case statement. Here is what I have so far:

CREATE PROCEDURE usp_TMIINV

declare @DecIn Int
declare @DecOut Int

AS

Select TAG#, dbo.FRINFM.GRADE,Gauge, Width, Weight,Cast(Weight/2000.00 as Decimal(4,2)) as Tons,Status,Cast([Cost-Cwt] as Decimal(4,2)) as [Cost-Cwt],dbo.tblGrades.Type as GaugeType, Space(2) as Type,Cast(0.00 as Float) as DecIn,
Cast(0.00 as float) as DecOut, Cast(0.00 as float) as DecAvg, Space(6) as GaugeFinal, Cast(Weight*[Cost-Cwt]/100 as money) as InvValue
Case Gauge
When charindex('/',Gauge)<>0 then
set @DecIn = substring(Gauge,1,(charindex('/',Gauge)-1)
set @DecOut = substring(Gauge,(charindex('/',Gauge)+1),99
update DecIn = @DecIn
update DecOut = @DecOut
update DecAvg = (@DecIn + @DecOut)/2

Else
End

From dbo.FRINFM
Join dbo.tblGrades
On dbo.FRINFM.GRADE = dbo.tblGrades.Grade
Where (Status=3) or (Status = 4)
GO

I want to create the variable based on the case statement then update the table with the new value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources