I have an Access Database that generates some reports from multiple SQL Servers including some Commercial products such as EZ-Cap. In order for the reports to work I have to run a process in Access that takes a full day to process this was scheduled to run Sunday morning @ 6 AM. Our Corporate Database team has made some changes and this process is no longer viable. Our local upper management relies on these reports and I have to get them back up ASAP. I'm trying to move most of the processing from my local Access DB to our Local SQL Server Database but not clear on a couple of things. I have a function in Access that calculates a specific benchmark our providers are tracked on (code is below). I'm not sure how to do this in a SQL Server Function Code I have so far is below just not sure where to go from there.
Code:
Public Function benchmark(ByRef strloB As String, Typeid As Long) As String
Dim rs As New ADODB.Recordset
Dim strsql As String, tmpstr
strsql = "Select Benchmark from tbl_benchmarks2 Where LOB = """ & strloB & """ and ID In "
Select Case Typeid
Case Is = 1
tmpstr = "(2,16,1)"
Case Is = 2
tmpstr = "(10,15,12)"
Case Is = 3
tmpstr = "(3,4)"
Case Is = 4
tmpstr = "(9,14)"
Case Is = 5
tmpstr = "(11,13)"
End Select
strsql = strsql & tmpstr
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
benchmark = ""
Do Until rs.EOF
benchmark = str(rs!benchmark)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
SQL Server
Code:
CREATE FUNCTION BENCHMARK
(
-- Add the parameters for the function here
@lob varchar(10,
@type numeric(18,0)
)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @bch varchar(50)
-- Add the T-SQL statements to compute the return value here
Select Benchmark from tbl_benchmarks2 Where LOB = strloB and ID In "
Case Typeid
when = 1 then (2,16,1)
when = 2 then (10,15,12)
when = 3 then (3,4)
when = 4 then (9,14)
when = 5 then (11,13)
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
END
GO